Forum Discussion
Combining INDEX MATCH Formulas
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.
I'm still confused. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Hopefully the attached link will take you to the spreadsheet: https://docdro.id/90NhFD1
- Hopefully this link will let you download a reduced content copy.: https://docdro.id/90NhFD1
- Wahooo.... Thank you. That looks like it works.
But SergeiBaklan 's formula doesn't refer to 'System & FL Codes'!B3:B112
- Actually.... It didn't work. Sorry. It was pulling over from the same table and not separate ones
Your data validation list is based on
but in Name Manager you have only few of the defined
For not defined names formula gives #REF!, for defined returns value or #N/A if nothing is found.
