Multi Criteria lookup

I need to search this list and from three user inputs and have it return the model number that meets the tests. User inputs will be height depth and trim. Also, if the user selects fire depth it will search that 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.

=IF(fire_depth<>"",
INDEX(A2:A200,MATCH(1,(B2:B200=height)*(D2:D200=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" <[email protected]> wrote in
message news:[email protected]...
>
> I need to search this list and from three user inputs and have it return
> the model number that meets the tests. User inputs will be height depth
> and trim. Also, if the user selects fire depth it will search that
> 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.
>
>
> --
> epotter
> ------------------------------------------------------------------------
> epotter's Profile:
http://www.excelforum.com/member.php...o&userid=36063
> View this thread: http://www.excelforum.com/showthread...hreadid=558532
>

{=IF(C12="Y",INDEX(A74:A112,MATCH(1,(B74:B112=E13)*(D74:D112<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.

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)

"epotter" <[email protected]> wrote in
message news:[email protected]...
>
>
{=IF(C12="Y",INDEX(A74:A112,MATCH(1,(B74:B112=E13)*(D74:D112<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.
>
>
> --
> epotter
> ------------------------------------------------------------------------
> epotter's Profile:
http://www.excelforum.com/member.php...o&userid=36063
> View this thread: http://www.excelforum.com/showthread...hreadid=558532
>