Forum Discussion
Combining INDEX MATCH Formulas
Which cell contains the drop down list? And what are the choices in that list?
Sheet4!E4 has the dropdown list. Selections from it are MAIN_EQUIPMENT and INSTRUMENT_ FIELD. I named the tables the same.
The tables are System & FL Codes'!B3:B112 and System & FL Codes'!F6:F466
I don't understand - you also use Sheet4!E4 as the value to look up in the MATCH part of the formulas.
Can you try to explain in more detail and more clearly what you want to do?
Also: the ranges used in INDEX and MATCH should have the same size.
Sorry for the confusion.
I have my tables on sheet called 'System & FL Codes'. The tables have been named MAIN_EQUIPMENT and INSTRUMENT_FIELD. The MAIN_EQUIPMENT look-up covers cells B4:B122. The INSTRUMENT_FIELD look-up cells cover cells F6:F466
I have a dropdown list in cell D4 where I can select MAIN_EQUIPMENT or INSTRUMENT_FIELD. This then allows me to select a Function using Data Validation =INDIRECT(D4), i.e. 'Analyser System'.
Sheet4!E4 is the reference cell. If that shows 'Analyser System', then cell I4 returns 'AA' from matching in the table.
The formula in cell I4 is
=(INDEX('System & FL Codes'!F6:F466,MATCH(Sheet4!E4,INSTRUMENT_FIELD,0))) works. (Image above)
If I use the formula
=(INDEX('System & FL Codes'!B3:B112,MATCH(Sheet4!E4,MAIN_EQUIPMENT,0))) than that works for the MAIN_EQUIPMENT list.
My problem is that I want to combine those 2 formulas so that they work with a dropdown list in cell D4.
