Weird Vlookup Behavior

[gkis2]

I know how to use VLookup and have done so successfully many many times. I am getting some weird behavior that is not right and was hoping someone knows how to fix it.

I use the data import feature on Excel to create an index for the rows of data and that index is, of course the first column. The range is named PPs. The format of the number is XXX.XXX or as example: 207.431. The last 3 digits are created using the line number of the table that is created on import. So a likely sequence might be:
… 206.422, 206.423, 206.424, 207.425, 207.426 … 207.430, 207.431, 208.432, 208.433...

The concept works pretty well. The data to the left of the first column can then be easily called … =Vlookup(B4 {say 207.425},PPs,55). The 55th column is pulled up and all is well with the world.

Except that sometimes (pretty frequently) Excel thinks it can't find one of these numbers. when that happens it find a close number which happens to always be the number just before. So when I call vlookup({207.426},PPs,55), I get the value for vlookup({207.425},PPs,55). I am using '{}' to represent the number. It actually comes from a formula that 'should' mathematically give the exact value. In fact I list out the values in the spreadsheet and they all display correctly. I've tried increasing number decimal places and it shows up perfectly. so 207.426 shows as 207.42600.

I was clever enough to include ,FALSE on the end to force exact match, so.. =VLOOKUP(B4,PPs,55,FALSE). That got rid of the 'wrong' result but just put in a blank. Not the intended result. Another weird thing. I used the exact same formula and just put it in a different cell on the sheet and it worked (there aren't any relative references at play).

The only thing I have thought of but hesitate is that its because the last digit is 'small' being in the thousandths place. I can reconfigure the index number if that is it. It would be a LOT of work so I would prefer to have some confidence that is it.

Also, I am subscribed to the 'Beta' Excel so I can take advantage of some newer features. but as far as I know, vlookup is far from new.

I think my computer is insane Please help.

 

[kweaver]

Could you post specifically what numbers you had in column A? And, what are you looking up in B4?
The number of decimal places shouldn't have any bearing on the result of the VLOOKUP search.

Is PPs a good range to look at? It's A1:??

 

[gkis2]

Could you post specifically what numbers you had in column A? And, what are you looking up in B4?
The number of decimal places shouldn't have any bearing on the result of the VLOOKUP search.

Is PPs a good range to look at? It's A1:??

The line under 'A likely sequence' shows the numbers that could be in the first (A) column.

Following is an exact sequence. The problem doesn't seem to depend on the specific numbers in first (A) column.

Not B4, I simplified a bit. E1 contains the first digit of XXX.XXX and another cell D1 contains the 2nd and 3rd digit. So the formula is =IF($D$3+0.001*(A3-1)<$E$3+0.001,($D$3+0.001*(A3-1))) D3 and E3 are the min and max in the series. 'A' column is just a series of integers 1 to 10. This makes the series below. In that case E1 would have been 1 and D1 would have been 2. 100*E1+D1 = 102. Within the range of data (PPs), 102 starts on the 11th row and ends on the 20th row. The next number for A in the range will be 103.021. That is data for the case where E is still 1 but D1 is now 3.

[TABLE="width: 72"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]

102.011[/TD]
[/TR]
[TR]
[TD="align: right"]102.012[/TD]
[/TR]
[TR]
[TD="align: right"]102.013[/TD]
[/TR]
[TR]
[TD="align: right"]102.014[/TD]
[/TR]
[TR]
[TD="align: right"]102.015[/TD]
[/TR]
[TR]
[TD="align: right"]102.016[/TD]
[/TR]
[TR]
[TD="align: right"]102.017[/TD]
[/TR]
[TR]
[TD="align: right"]102.018[/TD]
[/TR]
[TR]
[TD="align: right"]102.019[/TD]
[/TR]
[TR]
[TD="align: right"]102.02[/TD]
[/TR]
</tbody>[/TABLE]
PPs range is good. This actually works most of the time. By that I mean for most rows in the above or any sequence. That is what is so odd. For some reason it (MS Excel) suddenly can't find one of the index number. If I use ..,False for exact match it returns a blank since for whatever reason it can't find the index number. Of course, if I don't use ..,False then it just gives me a number that is 'close' which is the one before.

I appreciate your help. Thank You

 

[Eric W]

It may be due to the way Excel stores values. It stores numbers in a binary format, and rounds them as needed to appear the way you think they should. See here:

https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

So a calculated number may not be stored exactly the same as an entered number, even though they "look" the same. To get around this, you might want to save your numbers as text, and have your formula return a text value "123.456" instead of 123.456. It sounds like your numbers are indices and page numbers anyway, not values you need to perform mathematical operations on.

 

[joeu2004]

You are probably encountering binary arithmetic anomalies.

Be sure that all calculations are rounded to 3 decimal places, if that is accuracy that you expect. For example:

=IF($D$3+0.001*(A3-1)<$E$3+0.001,ROUND($D$3+0.001*(A3-1), 3))

PS.... That formula is wrong. It returns FALSE if the condition is false.

It is not clear to me if that represents the only calculations that you make. Be sure that the values in the left-most column of "PPs" are rounded. If they are constants, no problem. But if you use Fill Down, you should change it to a rounded calculation. And be sure that the lookup value (first parameter) is rounded. Again, if it is a constant, there is no problem.

 

[gkis2]

It may be due to the way Excel stores values. It stores numbers in a binary format, and rounds them as needed to appear the way you think they should. See here:

https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

So a calculated number may not be stored exactly the same as an entered number, even though they "look" the same. To get around this, you might want to save your numbers as text, and have your formula return a text value "123.456" instead of 123.456. It sounds like your numbers are indices and page numbers anyway, not values you need to perform mathematical operations on.

I'll have to dig into that. I glanced at some of it and it seems like it is talking about extreme places, not the thousandths place. And I have tried moving the number of places displayed and it shows nothing but zeros after the thousandths place. Still, seems plausible.

It is a lookup but I do some math and rely on MAX, MIN operations in Excel to pull the subset out of the Range. I'll want everything with 123.xxx so I have to go and find the biggest and smallest that start with 123.

After that is said and done I do just use them for lookup. I suppose I could transorm them to text just before doing the lookup. so VLOOKUP(Text(B4),PPs,55)? I wonder, would the format of the data in column A of PPs have to be text? If so then I'll be doing a lot of switching back and forth. I'll give it a go and see.

 

[kweaver]

I think trying rounding or .001*INT(A1*1000) might bring it to light.

 

[gkis2]

You are probably encountering binary arithmetic anomalies.

Be sure that all calculations are rounded to 3 decimal places, if that is accuracy that you expect. For example:

=IF($D$3+0.001*(A3-1)<$E$3+0.001,ROUND($D$3+0.001*(A3-1), 3))

PS.... That formula is wrong. It returns FALSE if the condition is false.

It is not clear to me if that represents the only calculations that you make. Be sure that the values in the left-most column of "PPs" are rounded. If they are constants, no problem. But if you use Fill Down, you should change it to a rounded calculation. And be sure that the lookup value (first parameter) is rounded. Again, if it is a constant, there is no problem.

If possible I would vote you genius of the year! I thought that I had eliminated something like this when I displayed the numbers with more digits and they showed all zeroes after the third place. I've tried it on a half a dozen screens and it seems to be working great. I was going to go through and change everything to ,,False to force exact match but now I don't think I have to. it just works.

And yes it displays False where the condition doesn't match but that's OK, that gets me just the data that I need. I suppose I could clean it up later so it shows blank instead but so far not necessary.

Thank You

 

[joeu2004]

You're welcome. To address some points without going into overwhelming detail (although I could, if you wish)....

I glanced at some of it [KB 78113] and it seems like it is talking about extreme places, not the thousandths place

Binary arithmetic anomalies can arise with any number of decimal places.

For example, IF(10.1-10=0.1,TRUE) return FALSE(!) because 10.1-10 is 0.0999999999999996 (+4.16E-17).

I have tried moving the number of places displayed and it shows nothing but zeros after the thousandths place

I thought that I had eliminated something like this when I displayed the numbers with more digits and they showed all zeroes after the third place

Excel formats (displays) only as many as 15 significant digits, an arbitrary limitation. The IEEE standard requires formatting up to 17 significant digits in order to avoid loss of precision when converting between binary and decimal.

For example, =20.1-10.1 in A1 displays 10.0000000000000. But MATCH(10,A1,0) returns #N/A, indicating they are not a binary match. In fact, SUM(A1,-10) returns about 1.78E-15.

(FYI, =A1-10 returns exact zero (0.00E+00). But that is due to an ill-conceived trick that Excel plays in a misguided attempt to hide some binary anomalies in limited and contexts.)

I could transorm them to text just before doing the lookup. so VLOOKUP(Text(B4),PPs,55)?

First, TEXT(B4) is not sufficient.

You might write TEXT(B4,"0.000"); but that is just another form of rounding to 3 decimal places.

Moreover, simply writing ""&B4 would be misguided, for the very reason that it does not round. For example, if B4 is =10.1-10, ""&B4" would be the text "0.0999999999999996".

No matter. Converting to text is not a good idea, IMHO, especially if you use the "approximate" form of VLOOKUP (4th parameter is TRUE or omitted).

For example, MATCH(3,{1,2,10}) correctly returns 2 because 3 is between 2 and 10. But MATCH("3",{"1","2","10"}) returns 3 because "3">"10" is TRUE (and because the text "numbers" are not in ascending text order). VLOOKUP results would be similar.

I think trying rounding or .001*INT(A1*1000) might bring it to light

0.001*INT(A1*1000) is no panacea.

For example, even if A1 is the constant 228.117 and =0.001*INT(A1*1000) in B1 appears to be 228.117, MATCH(A1,B1,0) returns #N/A, indicating they are not a binary match. In fact, SUM(B1,-A1) returns about 2.84E-14.

Arguably, INT(A1*1000)/1000 might work. But I would not rely on it.

 

[gkis2]

You're welcome. To address some points without going into overwhelming detail (although I could, if you wish)....



Binary arithmetic anomalies can arise with any number of decimal places.

For example, IF(10.1-10=0.1,TRUE) return FALSE(!) because 10.1-10 is 0.0999999999999996 (+4.16E-17).




Excel formats (displays) only as many as 15 significant digits, an arbitrary limitation. The IEEE standard requires formatting up to 17 significant digits in order to avoid loss of precision when converting between binary and decimal.

For example, =20.1-10.1 in A1 displays 10.0000000000000. But MATCH(10,A1,0) returns #N/A, indicating they are not a binary match. In fact, SUM(A1,-10) returns about 1.78E-15.

(FYI, =A1-10 returns exact zero (0.00E+00). But that is due to an ill-conceived trick that Excel plays in a misguided attempt to hide some binary anomalies in limited and contexts.)



First, TEXT(B4) is not sufficient.

You might write TEXT(B4,"0.000"); but that is just another form of rounding to 3 decimal places.

Moreover, simply writing ""&B4 would be misguided, for the very reason that it does not round. For example, if B4 is =10.1-10, ""&B4" would be the text "0.0999999999999996".

No matter. Converting to text is not a good idea, IMHO, especially if you use the "approximate" form of VLOOKUP (4th parameter is TRUE or omitted).

For example, MATCH(3,{1,2,10}) correctly returns 2 because 3 is between 2 and 10. But MATCH("3",{"1","2","10"}) returns 3 because "3">"10" is TRUE (and because the text "numbers" are not in ascending text order). VLOOKUP results would be similar.



0.001*INT(A1*1000) is no panacea.

For example, even if A1 is the constant 228.117 and =0.001*INT(A1*1000) in B1 appears to be 228.117, MATCH(A1,B1,0) returns #N/A, indicating they are not a binary match. In fact, SUM(B1,-A1) returns about 2.84E-14.

Arguably, INT(A1*1000)/1000 might work. But I would not rely on it.

I appreciate the additional info. So far using the round function has worked. I'll test several times as I develop the project and will likely come back to this post if more issues pop up. Excel is certainly a strange beast. I never would have imagined such inaccuracies. If nothing else this put the exclamation on Test, Test, Test!