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


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


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))