matt-gilbert
Member
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
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