VLOOKUP not working properly

NOTE: This thread is a tangential continuation from this one.

I do not know why my VLOOKUP column is only working for 4 cells. I've converted everything to numbers and checked for extra spacing, and I don't know what else to do.

For example, there is no difference between A21 and B31 (except for cell reference)

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

And yet, A21 yields a "N/A" error, and A31 gives the correct value.

Please see attached sheet.

This must be related to the Floating Point Precision issue.

Try
=VLOOKUP(ROUND(B21,0),'Rand #'!$A$2:$B$61,2,FALSE)

Hi,

You're getting #N/A values because what your looking up from column A (Locator_new sheet) doesn't exist in the lookup table (Rand # sheet) and you've asked for an exact match by specifying the FALSE parameter. If you use the TRUE parameter VLOOKUP will find the next closest value that is less than the value in column B (Locator_new sheet) in the lookup table (Rand # sheet) and return the appropriate value. So for example, if you us the TRUE parameter in cell A30 (Locator_new sheet) VLOOKUP will return 32 because the 15 doesn't exist in column A of the lookup table. It finds 10 as the next closest number that is less than 15 and returns the corresponding 32 from column B.

@Norie and oeldere

Are you guys looking at a different attachment than I am?
I don't see any decimal value in column B on locator_new sheet. Column B is all whole numbers with 16 decimals showing.

The value of B21 in Locator_new sheet is 10
On the Rand Sheet, A2 is 10
So the match is certainly there.

ok, now I see them further down (i never scrolled down past the rows related to the question, 21 and 31)
In row 21 and 31, there are no decimals, they are whole numbers.

Originally Posted by Jonmo1

This must be related to the Floating Point Precision issue.

Try
=VLOOKUP(ROUND(B21,0),'Rand #'!$A$2:$B$61,2,FALSE)

Jonmo1,

Thanks! That's what I needed!

You're welcome.