VLOOKUP based on 2 value criteria

[brendangroff]

I want to write a formula in cell E1 to pull in Part Number from column A if the width is greater than or equal to the value in column C and the length is greater than or equal to the value in column B. Multiple parts may fulfill these criteria, but I want it to pull in the part that most closely satisfies width first and then most closely satisfies the length.

Example: If the part sample is this:

PART LENGTH WIDTH
2101870 16.31 2
2101871 19.31 2
2103536 19.31 2
2101947 20.5 2
2101873 25.31 2
2101875 31.31 2
2103537 31.31 2
2101876 34.31 2
2103500 45 2
2101948 20.75 2.5
2103501 20.75 2.5
2103502 44 2.5

And I want the formula to choose the best part that fits 20.75 length and 2 width, I would expect it to choose 2101947. Is this possible?? Thanks!!

[Ace_XL]

If your width and length column are always sorted in ascending order (as you have in your sample) use

=LOOKUP(2,1/INDEX(($C$2:$C$13<=C19)*($B$2:$B$13<=C18),0),$A$2:$A$13)

[brendangroff]

If your width and length column are always sorted in ascending order (as you have in your sample) use

=LOOKUP(2,1/INDEX(($C$2:$C$13<=C19)*($B$2:$B$13<=C18),0),$A$2:$A$13)

I think it is close, but not quite there. The width has to be greater than the example of 2--If I change the example to 2.1 it still pulls in the 2 width part.

[brendangroff]

If your width and length column are always sorted in ascending order (as you have in your sample) use

=LOOKUP(2,1/INDEX(($C$2:$C$13<=C19)*($B$2:$B$13<=C18),0),$A$2:$A$13)

You know what I think I got it--just have to sort the data descending instead of ascending. Thanks!