function range

[robert]

PLEASE REPLY TO THIS ONE. I forgot to check the "Notify me of replies" box.

I need help!!! UGH!!!!

Example: 2 cells, one titled LEFT & one RIGHT. LEFT has a value in it of
2500 while RIGHT has 3500. They would look up in a chart that looks like this;

LEFT RIGHT $
250 250 40
500 500 100
1000 1000 200
2000 2000 400
3000 3000 600
4000 4000 800
5000 5000 1000

The LEFT 2500 would fall on the leftside between 2000 & 3000 while the RIGHt
3500 would fall between 3000 & 4000 on the rightside. The search would return
the level above the lowest value which in this case would be the 2000 row and
would be $400

I hope that this made sense and thank you for your assistance.

 

[Sean Timmons]

=VLOOKUP(MIN(VLOOKUP(A2,A3:A10,1),VLOOKUP(B2,B3:B10,1)),B:C,2)

should work. Assumes your entered left and right cells are in A2 and B2,
respectively and the chart is in A3 through C10.

 

[Rick Rothstein]

Since the ranges on your lookup table are the same, you only need one of
those columns. You don't say where you lookup table is, so I am assuming it
is on Sheet2 with the headers starting in A1. Make that table look like this

Left/Right $
250 40
500 100
1000 200
2000 400
3000 600
4000 800
5000 1000

Next, I have assumed your RIGHT and LEFT columns for your actual data are in
Columns A and B on whatever sheet they are located on (with headers in Row 1
and data starting in Row 2). Put this formula in C2 and copy down as
needed...

=LOOKUP(MIN(A2:B2),Sheet2!A$2:A$8,Sheet2!B$2:B$8)