MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Data formats

Posted by Neil J Bills on November 21, 2001 1:56 AM

I have been sent an Excel file which has data that I wish to use in a VLOOKUP table, no matter what format I try on the data the VLOOKUP table will not read the data, if I type over the data it reads immediatly.
I have tried copying and repasting values only but that has not worked and have tried most formats.
Any suggestions.

Posted by Dank on November 21, 2001 5:36 AM

Do you lookup keys look like numbers? If one file has 'numbers' that are actually text and the other file has numbers then VLOOKUP will not work no matter what you do to the formats of any cells. One way would be to use the VALUE formula in the 'number' cells that are actually text. Paste the formula down the side of your data,then copy, edit - paste special, values.

This may help,
If not you can email a sample workbook and I'll have a look.