lookup using multiple values

[borolo222]

Hi, I have a range containing a list of parts that are selected according to its length and weight capacity, each having a different reference, like this...

col A col B col C col D
length(in) ____ min weight(lb) ___max weight(lb) ___Reference
7 ___________ 4_______________ 8______________ 7-1
7 ____________8______________ 11______________ 7-2
7 ___________10______________ 12______________ 7-3
8 ____________5______________ 9_______________ 8-1
8 ____________9_____________ 12_______________ 8-2
8 ___________12_____________ 14_______________ 8-3

...and so on
so then i have a cell that has the length and another that has the weight requiered..
cel1: 8(length)
cel2:10 (weight)
it has to return me the value reference of column d: 8-2.

thanks for your help.

 

[Aladin Akyurek]

Try...

Control+shift+enter, not just enter:

=INDEX($D$2:$D$7,MATCH(1,IF($A$2:$A$7=J2,IF(K2>=$B$2:$B$7,IF($C$2:$C$7>=K2,1))),0))

 

[borolo222]

Thanks aladin, worked like a charm.
i need to learn to use the index function, where can i learn to use this function?

thanks very much.

 

[Aladin Akyurek]

Thanks aladin, worked like a charm.
i need to learn to use the index function, where can i learn to use this function?

thanks very much.

Excel's Help... Examples from the threads on this board.

BTW, it's the combined action with MATCH and IF that is important here.

 

[borolo222]

Excelent. thanks.