VLookup Within a Range

[BobA]

Well, I've done my due diligence and I'm stumped.

In cell c16 I have the following value: $132.52

In cell f14 I have the following formula:

=IFERROR(VLOOKUP(ROUND(C16,0),L2:N138,3),"")

40 $126.00 $129.00 $14.00
41 $129.00 $132.00 $14.00
42 $132.00 $135.00 $15.00
43 $135.00 $138.00 $15.00

The formula should round $132.52 to $133.00 and look for it in the table. Since, on line 42, $132.52 falls between $132.00 and $135.00 the answer would be $15.00. My formula gives an answer of $15.00

However, If c16 had a value of exactly $132.00 the answer should be $14. My formula gives an answer of $15.00 (I want the lowest possible value.)

And if the c16 contained $135.10 the answer should be $16, because $135.10 falls between $135.00 and $138.00.

How do I need to change the formula to get what I need?


Thanks for any help.

 

[Claus Busch]

Hi Bob,

Am Mon, 26 Aug 2013 20:18:03 -0700 (PDT) schrieb BobA:

In cell c16 I have the following value: $132.52

In cell f14 I have the following formula:

=IFERROR(VLOOKUP(ROUND(C16,0),L2:N138,3),"")

40 $126.00 $129.00 $14.00
41 $129.00 $132.00 $14.00
42 $132.00 $135.00 $15.00
43 $135.00 $138.00 $15.00

look in the column with the max value of the data range:
=VLOOKUP(ROUND(C16,0),M2:N138,2,1)


Regards
Claus B.

 

[BobA]

40 $126.00 $129.00 $14.00

41 $129.00 $132.00 $14.00
42 $132.00 $135.00 $15.00
43 $135.00 $138.00 $15.00

"look in the column with the max value of the data range:
=VLOOKUP(ROUND(C16,0),M2:N138,2,1)"

With this formula, if the lookup value is $132.00, then it gives the answer I want of $14.00. But if the lookup value is $133.00, then it still gives an answer of $14.00, but I want $15.00.

$133.00 lies between $132.00 & $135.00 on line 42.