Multi Criteria lookup

[epotter]

I need to search this list and from three user inputs and have it retur
the model number that meets the tests. User inputs will be height dept
and trim. Also, if the user selects fire depth it will search tha
column instead of the regular depth column.

Height = (User Input)
Depth = (User Input)
Trim = (User Input)
Fire Depth = (IF Y in cell ZZ1)
Model = (Excel Match)


MODEL HEIGHT DEPTH FIRE DEPTH TRIM
1815 18 5.5 6.375 1
1816 24 4 4.875 1.5
1817 24 2.625 3.5 3
1812 24 1.625 2.5 4
1818 24 1.125 2 4.5
1014 24 6.25 -1
1015 24 6 6.875 1
1016 24 4.5 5.375 1.5
1017 24 3.125 4 3
1012 24 2.125 3 4
1018 24 1.625 2.5 4.5
1013 27.1875 0 0
2014 27 8 -1
2015 27 7.875 8.5 1
2016 27 6.75 7.375 1.5
2017 27 5.5 6.375 2.5
2012 27 4 4.625 4


Tried to do index/match/vlookup only to get it to work half right.

Any help would be appreciated

 

[Bob Phillips]

=IF(fire_depth<>"",
INDEX(A2:A200,MATCH(1,(B2:B200=height)*(D2200=fire_depth),0)),
INDEX(A2:A200,MATCH(1,(B2:B200=height)*(C2:C200=depth),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[epotter]

{=IF(C12="Y",INDEX(A74:A112,MATCH(1,(B74:B112=E13)*(D74112<E12)*(E74:E112=E11),0)),INDEX(A74:A112,MATCH(1,(B74:B112=E13)*(C74:C112=E12)*(E74:E112=E11),0)))}


Getting a N/A value any ideas. C12 = Fire Rated check, E13 = height,
E12 = Depth, E11 = Trim style.

I added (E74:E112=E11) to check for Trim style. I can assume we are
looking for all true values of 1 to confirm the match(1,

Sorry but just learning excel.

 

[Bob Phillips]

It looks good. The errors suggests that you didn't find any rows that match
all 3 criteria.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)