3 Criteria Lookup

V

vinsdumonde

Lbs Scale Code Lbs
< USA 225
Click to expand...

EU USA JAPON
1 75 98.00 115.00 98.00
76 99 0.25 0.26 0.35
100 1099 0.30 0.31 0.49
1101 1199 0.45 0.46 0.57
1200 4400 0.78 0.79 0.90

I am looking for a formula that would find the value under code (in
this case USA) and then find the value under lbs (in this case 225)
from the lbs scale range and provide the cost per pound (in this case .
31)
Please keep in mind that corresponding value on the rate range must be
exact, otherwise it will calculate the wrong rate times the number of
pounds, resulting in over/uder billing amount.

Your input will be greatly appreciated! THANKS!
 
C

CLR

Try this.........

=VLOOKUP(D2,A4:E9,MATCH(C2,C4:E4,0)+2,TRUE)

Vaya con Dios,
Chuck, CABGx3
 
N

Niek Otten

Leave A1 blank.
A2: 1
A3: 76
etc

B1: EU
C1: USA
etc

A10: USA
A11: 225

Your formula:

=INDEX(B2:D7,MATCH(A11,A2:A7),MATCH(A10,B1:D1,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Lbs Scale Code Lbs
| > < USA 225
|
| EU USA JAPON
| 1 75 98.00 115.00 98.00
| 76 99 0.25 0.26 0.35
| 100 1099 0.30 0.31 0.49
| 1101 1199 0.45 0.46 0.57
| 1200 4400 0.78 0.79 0.90
|
| I am looking for a formula that would find the value under code (in
| this case USA) and then find the value under lbs (in this case 225)
| from the lbs scale range and provide the cost per pound (in this case .
| 31)
| Please keep in mind that corresponding value on the rate range must be
| exact, otherwise it will calculate the wrong rate times the number of
| pounds, resulting in over/uder billing amount.
|
| Your input will be greatly appreciated! THANKS!
 
Top