I have a cell with a dropdown list. I am trying to INDEX MATCH from 2 tables depending on what I select from the dropdown list. Tables are called MAIN_EQUIPMENT and INSTRUMENT_ FIELD. They are located on 'System & FL Codes' tab. The independent formulas below work, but is there a way to join them together? MAIN_EQUIPMENT index match formula=(INDEX('System & FL Codes'!B3:B112,MATCH(Sheet4!E4,MAIN_EQUIPMENT,0))) INSTRUMENT_ FIELD index match formula=(INDEX('System & FL Codes'!F6:F466,MATCH(Sheet4!E4,INSTRUMENT_FIELD,0)))

Daveway69 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

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

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

Daveway69 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?

Combining INDEX MATCH Formulas | Microsoft Community Hub

19 Replies

Daveway69
Copper Contributor
Jun 07, 2023
This worked, but is a bit long and messy.
=IFERROR(IF(D3="MAIN_EQUIPMENT",INDEX('System & FL Codes'!$B$3:$B$112,MATCH(H3,MAIN_EQUIPMENT,0)),INDEX('System & FL Codes'!$F$6:$F$466,MATCH(H3,INSTRUMENT_FIELD,0))),"")
HansVogelaar
MVP
Jun 05, 2023
Daveway69

Which cell contains the drop down list? And what are the choices in that list?
- Daveway69
  Copper Contributor
  Jun 05, 2023
  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
  - HansVogelaar
    MVP
    Jun 05, 2023
    Daveway69
    
    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.

Forum Discussion

Combining INDEX MATCH Formulas