• 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

  • Amount differs.xlsx
    14.3 KB · Views: 6
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

  • Copy of Amount differs.xlsx
    15.2 KB · Views: 1
Last edited:
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
 
thanks for your kind help, it worked,

now can you please help me in the next process
as if I use your formula in "V3" cell, it shows blank cell in result

please help
 

Attachments

  • Book1 - updated.xlsx
    44.4 KB · Views: 1
Back
Top