VLOOKUP #N/A Error that's new to me Excel 2010

[SuperLute58]

So I have the formula written exactly how it's worked many other times.

=VLOOKUP(B3, Sheet2!A:B, 2, FALSE)

I have found the value of B3 on Sheet2. I know it exists. I found it using the find feature. I have Sheet 2 sorted. Both Column B on my formula page and the Sheet2 column are are formatted the same. My table array is correctly aligned. However it's pulling back #N/A.

Please save the life of my computer so I don't have to cause it harm.

 

[PaddyD]

Hi - welcome to the board.

Could be a number of things. First suggestion - data types. check that =isnumber() returns same result for both instances of the value (if you're dealing with numbers). If that's not it, give us more details...

 

[Brian from Maui]

Instead of the Find feature try testing with something like

=A1=B1

Where A1 and B1 houses the data from the table

 

[SuperLute58]

It shows false on it. If I type the same number over the top of the source number, it shows true.

How can I change the formatting of the source number (A1 in your example, B3 in my formula) without having to manually type in all the numbers since there are 785 lines?

I tried copying and pasting out of values only. I changed the format of the cells. Nothing seems to be working on that front as well.

 

[Brian from Maui]

If these numbers are imported as text, you can multiply by 1 to coerce them into numbers. If they have leading or trailing spaces, use the TRIM function.

 

[SuperLute58]

The isnumber is returning false on it. Changing the format isn't affecting it. how Can I get it to change to a number?

 

[SuperLute58]

If these numbers are imported as text, you can multiply by 1 to coerce them into numbers. If they have leading or trailing spaces, use the TRIM function.

The multiply by 1 didn't work as I got #VALUE. TRIM didn't cut off anything.

 

[Brian from Maui]

In a empty cell enter 1. Copy the cell that houses the 1 and highlight the text numbers. Go to Edit (or right click) and Paste Special and tick Multiply.

See if that works

 

[SuperLute58]

In a empty cell enter 1. Copy the cell that houses the 1 and highlight the text numbers. Go to Edit (or right click) and Paste Special and tick Multiply.

See if that works

Still failing the =isnumber check after doing that.

 

[PaddyD]

does =len() return a value you expect for these 'numbers' - might be a hidden space? How are these values being generated - some details would help.