vlookup returns #N/A

[forcybo]

Hi all, hope you can help 'cos this is driving me nuts.

I use a lot of VLOOKUP formulae in the sheets i put together and, despite both the lookup value and table array being in the same format (usually text format), i often get an #N/A being returned.

Here's the fun part... if i then do something simple like access the lookup value cell (either by double clicking or pressing F2) and then hit return, then the vlookup calculation suddenly returns the correct value.

I'm convinced it's something to do with the way the cells are formatted but can't work out what. Anyone else come across this problem?

[arthurbr]

Hi,
can you post a sample of your data? There is probably a confusion between numbers and text ( even iif the format is identical)

[Richard Schollar]

Sounds like there's a mismatch between the way the cells are formatted and what Excel feels the value actually is (I take it these values are comprised entirely of numeric digits?). You can process the whole lot of values (rather than going to each cell individually with F2 and enter) by selecting the column and going Data>textToColumns>Finish.

Richard

[Shijesh Kumar]

Hi,

Try this....

Select the range containing the lookup value.
Click on Data --> Text to column
Click on radio button ( fixed width ) and then click on Finish.

Do this for lookup values in both the sheets i.e. the sheet in which you are using vlookup formula and the sheet where the data is stored.

[forcybo]

Perfect! Thank you guys.. this worked!!