• 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.

INDEX MATCH based on CONCATENATED cells

Hi,
I am creating a spreadsheet as follows;
Sheet 1 - User entry form. Range A2:E10 have dependant drop down lists and a fixed set of headings in Cells G1-Z1. Lookup results will be displayed in Cells G2:Z10.
Sheet 2 - Lookup database. VLOOKUP data in Range A2:E1000. HLOOKUP data in Range G2:Z1000.
On both sheets (in Column F) I have concatenated Columns A-E to create a single VLOOKUP value which I was hoping to use in an INDEX MATCH formula but this is not returning the desired result. Formula on Sheet1 Cell G2 is as follows;
INDEX(Sheet2!$F$1:$Z$1000, MATCH(concatenate(A2,B2,C2,D2,E2),Sheet2!$F$1:$F$1000,), MATCH(G$1,Sheet2!$F$1:$Z$1,))
I think it has to do with using concatenated values within the formula. Does anyone have any ideas on how to get around this? Many thanks. Matt
 
Hi Matt ,

Not returning the desired result is not very informative.

One possibility is that you are using the MATCH function with the third parameter omitted ; if you want an exact match , you need to use 0 here , as in :

=INDEX(Sheet2!$F$1:$Z$1000, MATCH(concatenate(A2,B2,C2,D2,E2),Sheet2!$F$1:$F$1000,0), MATCH(G$1,Sheet2!$F$1:$Z$1,0))

Narayan
 
Back
Top