SOLVED

vlookup closet strange mismatch (just two value: 0.051 & 0.071)

Copper Contributor

I have a vlookup question. Attached is a simple worksheet. Why does Cell G7 get the value 28 when I expect the lookup result to be 30?

The formula is lookup the closet match value on A2 to C12.

column F =VLOOKUP($E2,$A$2:$C$12,3)vlookupValueIncorrect2024-07-04_181918.jpg

6 Replies

@Kevin_Lam 

Please set both columns A and E to display 15 decimal places.

I suspect that you'll see that the value of E7 is a tiny bit smaller than the value of A7, and that the value of E9 is a tiny bit smaller than that of A9.

best response confirmed by Kevin_Lam (Copper Contributor)
Solution

@Kevin_Lam 

Also: try changing the formula in A3 to =ROUND(B2+0.001, 3), then fill down.

It is due to rounding most likely as the data in col.E might not be exactly as in col.A

Your can easily formulate a check to see if they match if the following is TRUE =A7=E7

@HansVogelaar 

 

thank you for your reply, even 30 decimal places. it is no lucky

Thank you, you saved my life. round function is work.

thank you for your reply, yes it is strange rounding issue. even though =A7=E7 is return true, the mismatch error can be rectified follow @HansVogelaar rounding suggestion.
1 best response

Accepted Solutions
best response confirmed by Kevin_Lam (Copper Contributor)
Solution

@Kevin_Lam 

Also: try changing the formula in A3 to =ROUND(B2+0.001, 3), then fill down.

View solution in original post