Index Match - please assist for complicated lookup

[yogi_himalayan]

Hello everyobody, I seek your assistance to solve lookup issue for which I could not get any clue even after spending more than half day!

BACKGROUND
I have attached spreadsheet. it has 2 tabs "Dynamics" and "Statement". "Dynamics" consists the data we have in our system and "Statement" consists data received from actual supplier.

Currently we have Vlookup formula on column "J" of "Statement". Formula is hard coded to "Dynamics!$H$:$H$632" and it works fine.

But this formula has limitation because required data in not always located in column H of Dyanmics tab. This data with heading of "Tax Invoice" can be anywhere between column A to W of Dynamics Tab.

REQUIRED

Since both tab has heading "Tax Invoice" in common, I am looking for formula in column J of "Statement" which will look value in column with "Tax Invoice" in "Dynamics" Tab

Thanks

Query.xlsx

[AlKey]

Question. What is your VLOOKUP formula in column J supposed to do? This triple nested formula does 3 lookups in the the same range and it doesn't do anything more than just a single VLOOKUP would do.

[benishiryo]

hi there. not that VLOOKUP cannot do the job, but it is unnecessary to use VLOOKUP in this case. VLOOKUP is more like if you wish to know the Amount To Settle for eg in Dynamics Sheet for the particular Tax Invoice number. so it goes like:
=VLOOKUP(VALUE($D4),Dynamics!$H$1:$P$632,9,FALSE)

so if it finds the Tax Invoice in Column H, it returns you the 9th column from there, i.e. Column P.

unless you really want to return the Tax Invoice itself. i'm not sure. you could use a COUNTIF to count if the Tax invoice exist.
=COUNTIF(Dynamics!H:H,D4)

then use IF to decide what text you wanna show if it exist. for eg
=IF(COUNTIF(Dynamics!H:H,D4)>0,"Exist","Nope")

and then it looks like if you can't find the Tax Invoice, you want to see if the Sales Order is there. it seems like none is. but if that's what you want, then:
=IF(SUMPRODUCT(COUNTIF(Dynamics!H:H,D4:E4))>0,"Exist","Nope")

to find the column with Tax Invoice, try:
=IF(SUMPRODUCT(COUNTIF(INDEX(Dynamics!$A:$Y,,MATCH("Tax Invoice",Dynamics!$A$5:$Y$5,0)),D4:E4))>0,"Exist","Nope")
or if you still want to return the particular Tax Invoice or Sales Order, try:

Please Login or Register  to view this content.

[yogi_himalayan]

Thank you very much benishiryo. It works perfectly the way I wanted. Very much appreciated