• 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
 

NARAYANK991

Excel Ninja
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
 
Top