# VLOOKUP not working properly

1. ## 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.

2. ## Re: VLOOKUP not working properly

This must be related to the Floating Point Precision issue.

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

3. ## Re: VLOOKUP not working properly

I'm confused, in A21 you are looking up the value in B21 which is 10 and in A31 you are looking up the value in B31 which is 16.

Anyway, there is a rounding problem, you'll see it if you increase the no of decimals in column B, but you could try using ROUND to deal with that.

=ROUND(SUMPRODUCT((C2 >=Row)/COUNTIF(Row,Row)),0)

4. ## Re: VLOOKUP not working properly

b21 in sheet locater new does not excist in column A of sheet rand #.

That is why you get the result N/A (not available).

b31 in sheet locater new excist in column A of sheet rand #. (and that is why it returns the value 30, the value from column B (the 2 in the VLookup formula).

5. ## Re: VLOOKUP not working properly

Most of the numbers don't exist in the Lookup Table.

6. ## Re: VLOOKUP not working properly

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.

7. ## Re: VLOOKUP not working properly

@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.

8. ## Re: VLOOKUP not working properly

Jonmo1

I don't know how many decimals I went out to but I started seeing values like this,

70.000000000000100000000000

which is in row 137 and this

312.999999999999000000000000

in row 667.

Just checked and it was at about 13 decimal places I started seeing these, and similar values.

9. ## Re: VLOOKUP not working properly

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.

10. ## Re: VLOOKUP not working properly

Just spotted Norie's numbers!

11. ## Re: VLOOKUP not working properly

look at attachment
if you see #N/A = value from B2 doesn't exist in defined range

12. ## Re: VLOOKUP not working properly

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!

13. ## Re: VLOOKUP not working properly

You're welcome.

14. ## Re: VLOOKUP not working properly

Harrumph. PCs are thick. they can only (sometimes) count in binary. The Venus Fly Trap can count to 2....

Anyhow (I can't explain it...) but rounding works.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1