Combining INDEX MATCH Formulas

[Daveway69]

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

[#2]

@Daveway69 

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

[#3]

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

[#4]

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

[#5]

@Hans Vogelaar 

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. 

[#6]

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

[#7]

@Hans Vogelaar 

Hopefully the attached link will take you to the spreadsheet: https://docdro.id/90NhFD1 

[#8]

Hopefully this link will let you download a reduced content copy.: https://docdro.id/90NhFD1

[#9]

@Daveway69 

Perhaps

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

[#10]

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

[#11]

@Daveway69 

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

[#12]

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

[#13]

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

[#14]

@Daveway69 

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.

[#15]

@Daveway69 

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

[#16]

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.

[#17]

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.

[#18]

I will look into that. Good suggestion.

[#19]

@Daveway69 

For the defined names it shall work.

[#20]

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