vlookup and formatting

[smking204]

I'm having trouble getting vlookup to recognize a lookup value. Here's the situation:

• I built a table and changed the numbers to text format
• I built a vlookup: =VLOOKUP(H1, 'Tables - Extra'!B:D, 3, 0)
• I've verified that my table array ('Tables - Extra'!B:D) is working just fine, as far as I can tell.
• No matter what I try, the result is #N/A as long as I'm referencing H1 or any other cell in the table for my Value Lookup
• Now, I've copied my table and pasted values only into a new sheet, trying to start fresh.
• In this fresh sheet, where every value is pasted as general format, I get the same problem, until I retype the number in the cell--suddenly vlookup will recognize it now. I can even change the format to text, or leave it as general.
• Thinking I've done something to remedy the problem, I go back to the original table and try the same thing, retyping my values, trying various format changes... with no luck.

What is causing this hang-up? What is a quicker way to fix it than retyping and reformatting my entire table? I hope this has been enough information, thanks in advance for the replies!

 

[Joe4]

Welcome to the Board!

Note that in order to make a VLOOKUP work, the values you are comparing/matching MUST be the same data type.
You can only compare numbers-to-numbers, and text-to-text.

Changing the format of a column AFTER the data has been entered will NOT change the format of those data entries!
It would need to be re-entered (like you saw when you re-typed them).

However, you can change the data of the whole column at once by selecting that column, and choosing "Text to Columns" from the Data menu.
Once there, go to step 3, and you can choose "General" if you want to make the values numbers, or "Text" if you want to make them text.

So, follow that process to make the two lists compare like data types, and everything should work out.

 

[alz]

Or another way to change number which appear as text

Type 1 in any blank cell => Copy => Select the range which you want to change => paste special => value and multiply => Ok

 

[JamesCanale]

I was able to reproduce the error, and did this:

=VLOOKUP(TEXT(F2,"0.00"),B1:C29,2,FALSE)

but the formatting has to work exactly. so 2.12 will lookup in this example, but 2.1 won't. If you can format your lookup table so it's always the same, it could work.