Darren gave Pauline a tip on how to change them all in one go - have
you tried that?

Alternatively, if your lookup table contains text values that look
like numbers and your lookup value is a proper number, then you could
modify your VLOOKUP formula like this:

=VLOOKUP(A1&"",lookup_table,2,0)

where A1 contains your lookup value, and the formula thus converts the
proper number to text.

Hope this helps.

Pete

On Mar 20, 8:45*pm, PianistFromJersey
wrote:
I have a similar problem even when I have formatted all the values in a
column with the same format - either number or text. The VLOOKUP and MATCH
functions sometimes do not work unless, like Pauline Warner below, I press
F2/Enter in each cell, or click on the edit bar at the top of the screen,
then Enter (They give #N/a eerror). It is as if the spreadsheet does not know
what the format is until you hit Enter.

"Darren Bartrup" wrote:
When you get the message there's an exclamation mark that pops up - with all
your values selected press the exclamation mark to get a pop-up menu and
select convert to number.

This will convert all the values to number format in one hit.

A quicker way, but not the ideal solution - which would be to have your
report writer place them as the same format as the VLOOKUP in the first place.

Question

* our report writer returns numbers in general format however initially each
cell has an Excel "error" note (green triangle in top left corner) to say the
numbers in the cell are formatted as text or preceded by an apostrophe.
VLOOKUP won't work until this "error" is cleared. I can clear it by pressing
F2 and Enter within each cell and then VLOOKUP works fine but is there a
quicker or automated way to do this as it gets to be a pain when you have
2000 rows of data.

thanks
--
Pauline Warner