Vlookup Value Issue

**yawnzzzz** · 03-30-2011, 08:22 AM

Hi,

I have a value in my worksheet that I'm trying to find with a vlookup. I cannot change the value, so this has to be solved within the vlookup.

Some things I know:
-Searching for 220 = #N/A
-Searching for "220" = #N/A
-Hitting enter on cell, then searching for 220 works (but I can't do this)
-Trimming the value, then searching for "220" works (but I can't do this)
-Searching for " 220" = #N/A
-LEN only returns 3 for the value

I've attached an example.

**Cutter** · 03-30-2011, 08:50 AM

Changing your formula to:

=VLOOKUP(C1,$C1:$C1,1,FALSE)

also works

**yawnzzzz** · 03-30-2011, 09:12 AM

Originally Posted by Cutter

Changing your formula to:

=VLOOKUP(C1,$C1:$C1,1,FALSE)

also works

Correct. This won't solve my issue though because I'm deriving the value from elsewhere. So, I really need to know how to type in the equivalent of C1.

**Cutter** · 03-30-2011, 09:21 AM

Can you type in the equivalent in another cell and refer the VLOOKUP() to that cell?

I don't know why the direct numeric reference in the formula doesn't work. It is not a numeric/text issue because C1 is formatted as General and =ISNUMBER(C1) returns TRUE.

**yawnzzzz** · 03-30-2011, 09:44 AM

Originally Posted by Cutter

Can you type in the equivalent in another cell and refer the VLOOKUP() to that cell?

I don't know why the direct numeric reference in the formula doesn't work. It is not a numeric/text issue because C1 is formatted as General and =ISNUMBER(C1) returns TRUE.

Basically, I'm going to have a number such as 220.9 as my starting value for the vlookup. I can round it down, change it to text, add spaces, etc., but I must use it to find that 220 cell.

**martindwilson** · 03-30-2011, 09:50 AM

cant say i can see whats causing it but just select the coloumn then data/text to columns click finish should fix it for entire column

**Cutter** · 03-30-2011, 09:55 AM

OK, so you're making that manipulation in a cell. (You wouldn't want to change it in such a way that it doesn't match the values in your lookup range)
And you would use that cell reference in your VLOOKUP().
That doesn't work?

**yawnzzzz** · 03-30-2011, 02:39 PM

Originally Posted by martindwilson

cant say i can see whats causing it but just select the coloumn then data/text to columns click finish should fix it for entire column

That's not really an option. The table is going to be pasted in from another source, and my reference to find it isn't static either. So, my only option is to be able to come-up with the correct value in my vlookup to where it finds that unmanipulated cell.

**bentleybob** · 03-30-2011, 02:52 PM

Have you tried formatting the entire Column C to something like Accounting with no decimal places?

**yawnzzzz** · 03-31-2011, 09:06 PM

I saved it as an Excel 2007 file, so I could unzip it and look at the XML.

Here's what I found:

Please Login or Register  to view this content.

You can see that for whatever reason the value is actually 220.00000000000003 even though this isn't visible, and if you touch the cell it corrects itself.