Formula That Returns Corresponding Value within Table

[pj]

Looking for a formula that will find (lookup) where a specific price falls within a range of prices shown in Columns A and B, and then return the corresponding value shown in Column C (see below).


In the following example, January’s price is $3.833. What formula would return “7.50%” because the price ($3.833) is between $3.79 and $3.91 in the accompanying table?


Jan. $3.833

[pre]

Column A  Column B       Column C
At Least  But Less Than	 Surcharge
0	   $2.23 	 0.00%
$2.23 	   $2.35 	 1.00%
$2.35 	   $2.47 	 1.50%
$2.47 	   $2.59 	 2.00%
$2.59 	   $2.71 	 2.50%
$2.71 	   $2.83 	 3.00%
$2.83 	   $2.95 	 3.50%
$2.95 	   $3.07 	 4.00%
$3.07 	   $3.19 	 4.50%
$3.19 	   $3.31 	 5.00%
$3.31 	   $3.43 	 5.50%
$3.43 	   $3.55 	 6.00%
$3.55 	   $3.67 	 6.50%
$3.67 	   $3.79 	 7.00%
$3.79 	   $3.91 	 7.50%
$3.91 	   $4.03 	 8.00%
$4.03 	   $4.15 	 8.50%
$4.15 	   $4.27 	 9.00%

[/pre]
Thanks

 

[Luke M]

Welcome to the forums PJ! The function you are looking for is the LOOKUP function with has this syntax:

=LOOKUP(FindValue,SearchColumn,ReturnColumn)


Assuming the value for January is in D2, XL format would look like:

=LOOKUP(D2,$A$2:$A$19,$C$2:$C$19)


Note that you may need to adjust the number format for the cell with the formula to get it to display in percentages with 2 decimals.

 

[pj]

Thanks for your reply. One observation is that you ignore the middle column. Alternatively, could you use VLookup, which would include the middle column? Also, is there a conflict with the data, in that, column A (the "at least" number) is the same number as the next value in the column B (the "but less than" number)? Example, first row shows $0 - $2.23 in first row, then row 2 begins with $2.23. Should the next row begin with $2.24 instead?

Thanks again

 

[Luke M]

It really depends on how you are defining the limits. I was assuming a logic of

Col A <= x < Col B

with x being the value you are looking for. You could certainly use a VLOOKUP function, but it's a bit of overkill (slightly less efficient). Since I'm ignoring the middle column, there's no conflict either from a formula standpoint. From a human-readable viewpoint, you might want to change things so you don't have overlaps. If a value of $2.23 needs to have a surcharge of 0.00%, then yes, you need to adjust col A so that next row begins with $2.24


The trick with using a lookup type table is that each row needs to be the threshold for next level.

 

[Jai9]

Hi,


Try,


=INDEX($A$1:$C$19,MATCH(3.833,$A$1:$A$19,1),3)


Your column says "But less than" which means it would be 2.22 at the max for first row. You would not get 2.23 for first row.


So I dont think there is no need to begin with 2.24 in the next row.


Jai

 

[pj]

Many thanks again. Jai--What's the difference using "Vlookup," "Lookup" or "Index?" When is it appropriate to use each one over the other?

 

[Faseeh]

Hi Pj,


I think you need to reformat the table because in this table the Range is overlapping. For example for the very first row, if you are looking up for 2.23 what surcharge will you charge 0.00% or 1.00%. In my opinion the table should look like:

[pre]

Column A  Column B       Column C
At Least   But Less Than  Surcharge
0	   $2.23 	  0.00%
$2.24 	   $2.36 	  1.00%
$2.37 	   $2.48 	  1.50%[/pre]

....so on.


In such a case you will not confront with such a problem, you can use:


=VLOOKUP(3.38,A3:C20,3)

..quite safely.


Regards,

Faseeh

 

[Luke M]

LOOKUP is the simple/fast function. Works only when the table is sorted

VLOOKUP offers more choices. You can choose which column from within an array to return, and whether to find closest match (if so, table needs to be sorted) or exact match

INDEX/MATCH works very similar to VLOOKUP in that you specify the same arguments. MATCH can return closest value (again, would need to be sorted) or an exact match.


There's some debate within the community as to whether VLOOKUP or INDEX/MATCH is more efficient. VLOOKUP is shorter to write, but INDEX/MATCH gives a little more flexibility as you can return a column to the left of where you are searching.


So which one to use? From a heavy user standpoint, use the one that takes least amount of calcluation power combined with whichever one you understand the best. I may be able to write an awesome formula, but if someone else can't understand it to update/fix it later, it is not of as much use.


More reading on VLOOKUP vs INDEX/MATCH

http://exceluser.com/blog/1107/why-index-match-is-far-better-than-vlookup-or-hlookup-in-excel.html

http://www.excelguru.ca/forums/showthread.php?132-INDEX-MATCH-versus-VLOOKUP

 

[Jai9]

Hi,


check out this link to get more information about lookup functions.


http://chandoo.org/wp/2012/03/30/comprehensive-guide-excel-vlookup/


Jai

 

[Luke M]

Example of simple LOOKUP. If you change your table to look like this

[pre]

Amount	Surcharge
0	0.00%
$2.23 	1.00%
$2.35 	1.50%
$2.47 	2.00%
$2.59 	2.50%
$2.71 	3.00%
$2.83 	3.50%
$2.95 	4.00%
$3.07 	4.50%
$3.19 	5.00%
$3.31 	5.50%
$3.43 	6.00%
$3.55 	6.50%
$3.67 	7.00%
$3.79 	7.50%
$3.91 	8.00%
$4.03 	8.50%
$4.15 	9.00%

[/pre]
Formula becomes:

=LOOKUP(D2,A2:B19)

and I don't think it gets much shorter than that. =)

 

[pj]

Excellent responses to all. Thanks again.