Lookup Data from a Table

[cmb3587]

I have a table of data:

Nom. Min. Max. Part #
0.25 300 580 25L177
0.25 300 800 25M177
0.50 750 1800 50L177
0.50 1000 2750 50M177

I want the user to be able to plug in 2 values:

Nom. 0.25
Load 600

I want a cell that will spit out the value in column 4 that looks up the value in column 1 (0.25) then finds the appropriate range using columns 2 and 3 (300 800) and returns the part number (25M177)

Vlookup only returns the first instance of 0.25 and doesnt allow me to use a range found in two seperate columns. Is there a way to do this? I have looked up various VB codes to help but have found nothing that works.

[NBVC]

Assuming the table is in A1:D5,

Try:

=INDEX($D$2:$D$5,MATCH(1,INDEX(($A$2:$A$5=A11)*($B$2:$B$5<=A12)*($C$2:$C$5>=A12),0),0))

Where cell A11 contains the Nom. and A12 contains the Load to search for...

Adjust ranges and references to suit.

[cmb3587]

Works great so far! Thanks..will post another reply if I run into any trouble. I need to tweak it to meet a few other needs. I may need an iserror function in there because if the load is greater than the range specified there is 2 other columns that have higher load ranges so if there is an error searching the range it will search the range in 2 diff columns...but I should be able to figure that out.


Thanks again!