• 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


  • 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


GraH - Guido

Well-Known Member

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.


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