# Combining INDEX MATCH Formulas

Copper Contributor

# Combining INDEX MATCH Formulas

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

19 Replies

# Re: Combining INDEX MATCH Formulas

Which cell contains the drop down list? And what are the choices in that list?

# Re: Combining INDEX MATCH Formulas

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

# Re: 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.

# Re: Combining INDEX MATCH Formulas

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.

# Re: Combining INDEX MATCH Formulas

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?

# Re: Combining INDEX MATCH Formulas

Perhaps

``=(INDEX('System & FL Codes'!F6:F466,MATCH(Sheet4!E4,INDIRECT(D4),0)))``

# Re: Combining INDEX MATCH Formulas

Wahooo.... Thank you. That looks like it works.

# Re: Combining INDEX MATCH Formulas

But @SergeiBaklan 's formula doesn't refer to 'System & FL Codes'!B3:B112

# Re: Combining INDEX MATCH Formulas

Actually.... It didn't work. Sorry. It was pulling over from the same table and not separate ones

# Re: Combining INDEX MATCH Formulas

I tried it but you are right. It never pulled over from the other table.

# Re: Combining INDEX MATCH Formulas

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.

# Re: Combining INDEX MATCH Formulas

To handle errors formula could be

``````=IF(
ISERROR(ROWS(INDIRECT(D4))),
"no range",
IFERROR(
INDEX(
'System & FL Codes'!F6:F466,
MATCH(Sheet4!E4, INDIRECT(D4), 0)
),
"no such value"
)
)``````

# Re: Combining INDEX MATCH Formulas

Sorry,I am not what you intent to do.
If for pick item easily,why not consolidate sub tables in System & FL Codes to one sheet first?
Then it is easy to handle the dropdown box.

# Re: Combining INDEX MATCH Formulas

I only have a couple defined as I need to removed some of the items from the dropdown selection. i.e. FIRE_AND_GAS will not be needed for my project.
Once I understand how I can combine multiple INDEX MATCH formulas, then I intend to add to it.

# Re: Combining INDEX MATCH Formulas

I will look into that. Good suggestion.

# Re: Combining INDEX MATCH Formulas

For the defined names it shall work.

# Re: Combining INDEX MATCH Formulas

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