Searching multiple tables with multiple criteria

[Sampepper92]

Hi all,

I'm trying to find a formula that will allow me to search multiple tables with multiple criteria e.g:

I have a main sheet with multiple criteria: shop name, engine type, type of service.

I then need to use these criteria to search within a number of different tables set out like:
Engine 1
quick service long service
Shop 1. 22. 34
Shop 2. 45. 56
Shop 3. 56. 78

Engine 2
Quick service. Long service
Shop 1. 14. 36
Shop 2. 90. 67

Thanks so much

 

[Special-K99]

With your data on Sheet1
Engine Type being 1 or 2
and Engine 1 in Sheet2!A1 and Engine 2 in Sheet2!A7

in Sheet1!D1
=VLOOKUP(A1,IF(B1=1,Sheet2!$A$3:$C$5,Sheet2!$A$9:$C$10),LOOKUP(C1,{"Long service","Quick service"},{3,2},0)

B1 ( 1 or 2) idneitifies which table to get info from
C1 idientifies which column to extract 3 or 2 - LOOKUP inline array {} must be alphabetical thats why "Long" is before "Quick" hence resulting columns must be reversed 3 then 2, NOT 2 then 3

 

[Sampepper92]

Thank you. If I were to expand the criteria to 4 or more tables, how would this change the formula?

 

[Special-K99]

You'll need to nest some IF statements to include other tables.

Or this may work for 2 tables (untested)

=VLOOKUP(A1,INDIRECT(LOOKUP(B1,{1,2},{"Sheet2!$A$3:$C$5","Sheet2!$A$9:$C$10"})),LOOKUP(C1,{"Long service","Quick service"},{3,2},0)

Expand the contents of both {} to incorporate other Engine types 3,4,5, along with accompanying sheet ranges in the second set of brackets