• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Amount differs in result having same voucode

hello sir, can you please help me in this formula as i need the different amount from the table, but it shows the same amount as of previous in last line of result, Help me please

please check the attached file
 

Attachments

GraH - Guido

Well-Known Member
Hi,

The formula in N24 does not need to be an array formula.
You are using MATCH(L37,abc[VOUNO],0) so that finds the exact match.
Thus it will return the index for the first value found where L37 = abc[VOUNO], being 13. So the index part will always return 2017, since [VOUNO] with the value 195789 appears twice in your table.

Is it an error in the table, or do you need another formula to handle this scenario?

EDIT: possible like formula in file to handle 2 or more occurrences of [VOUNO] in table abc. Perhaps you'd need to do the same for table def.
 

Attachments

Last edited:

GraH - Guido

Well-Known Member
Might be, replace this AGGREGATE(15,6,ROW(abc[VOUNO])/(abc[VOUNO]=L23)-1
by AGGREGATE(15,6,ROW(abc[VOUNO])/(abc[VOUNO]=L23)-5
 
Top