VLOOKUP error phenomenon... when look_up value sum = 1.14

[poolio]

I've recently discovered an odd phenomenon when using VLOOKUPs.
If I use a SUM to determine my lookup_value then it always seems to work except when that sum comes to 1.14...

I basically use this when extracting reptitive data from a list and want to tabulate it. The lists increase in number by an increment of 0.01 - i.e. A1= 1, A2=A1+0.01 etc... until a threshold is met then it restarts on the next whole integer... i.e. A30=2, A31=2.01 etc etc...

In my table I have whole numbers across the top and 0.01's down the side.

So my VLOOKUP formula in the table, produces a sum of the row header (e.g. 1) and my column 'header' (e.g. 0.12), producing a lookup_value of (e.g. 1.12) which looks down the table of data and returns the relevant column that I specify.... This works like a dream for every number between 1.00 and 51.20 (in my e.g.) with the exception of 1.14..!?!

Can anyone explain this mystery?

 

[Aladin Akyurek]

Re: VLOOKUP error phenomenon... when look_up value sum = 1.14 - SORTED!

What in tarnation is a "floating point arithmetic issue"??

I think you meant...

=VLOOKUP(ROUND($G338+I$336,2),'Predicto Tab'!$R:$U,3)

Bingo! thanks chaps
JP

Indeed. Thanks for providing feedback. I see you omitted the 4th argument of VLOOKUP. That boils down to having 1 or TRUE by default for approximate matching in a sorted table.

 

[poolio]

No problems!

I've never truly understood / considered the [range_lookup]... TRUE,1 / FALSE,0 options in VLOOKUP... I've always just put in FALSE, as I've assumed that means exact match only... I've always wondered what else you would want?

But I guess in this scenario where the value I'm looking for may not be sufficiently exact (i.e. as I've just learned from Andy's handy link - where the exact value reported is influenced by a binary encryption method!!) then when looking for 1.14, in a list where there is a possibility I might have a 1.1400000000000000001 instead, will Excel pick that value as the closest option, when I omit the FALSE [range_lookup] option...?? or might it go the other way and look for the next value higher / lower in the list??? i.e. 1.13 or 1.15....??

Pretty academic now I have the solution (for which I will remain eternally grateful!)

 

[Aladin Akyurek]

No problems!

I've never truly understood / considered the [range_lookup]... TRUE,1 / FALSE,0 options in VLOOKUP... I've always just put in FALSE, as I've assumed that means exact match only... I've always wondered what else you would want?

But I guess in this scenario where the value I'm looking for may not be sufficiently exact (i.e. as I've just learned from Andy's handy link - where the exact value reported is influenced by a binary encryption method!!) then when looking for 1.14, in a list where there is a possibility I might have a 1.1400000000000000001 instead, will Excel pick that value as the closest option, when I omit the FALSE [range_lookup] option...?? or might it go the other way and look for the next value higher / lower in the list??? i.e. 1.13 or 1.15....??

Pretty academic now I have the solution (for which I will remain eternally grateful!)

Given

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64 align=right>2%</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>6%</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>90</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>12%</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>200</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>15%</TD></TR></TBODY></TABLE>

in A2:B5, we need match-type = 1 for a value of 25 means 2%, 90 12%, and 300 15%.

=LOOKUP(E2,$A$2:$A$5,$B$2:$B$5)

=VLOOKUP(E2,$A$2:$B$5,2,1)

=INDEX($B$2:$B$5,MATCH(E2,$A$2:$A$5,1))

all will yield a result according to the bolded specification.

 

[poolio]

So your examples effectively find any value lower* than the one you specify.

In my case, I can't I be sure how the "floating point arithmetic issue" will work out?

i.e. Not sure I will know if my search value is:
1.13999999999999999999999
or
1.140000000000000000000001

And depending which way it falls, this will impact on the return value.

i.e. if my lookup value is calculated as slightly >1.14, then no problem but if it's slightly <1.14, then the lookup will return the corresponding value for 1.13.

Thanks for clearing that up - very useful. A***