SOLVED

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

Copper Contributor

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

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)

6 Replies

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

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

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

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

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

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

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

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

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

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

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

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

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

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