Why is it that often this formula doesn't work?
Do I need to clean up cells, or do they need to be in the same format?



--
Frank


------------------------------------------------------------------------
Frank's Profile: http://www.excelforum.com/member.php...nfo&userid=464
View this thread: http://www.excelforum.com/showthread...hreadid=497848

It would help if you could advise the syntax of the formula you are trying to
use and what you mean by 'it doesn't work', i.e. is it returning #N/A or is
it returning the wrong result?

In the meantime, have you checked that the cell you are looking up and the
range you are searching are both text format, or are both numeric - if this
isn't the case you will likely get #N/A a result.

Also, the first column of the range you are searching must contain the data
you are searching for......

"Frank" wrote:


Why is it that often this formula doesn't work?
Do I need to clean up cells, or do they need to be in the same format?



--
Frank


------------------------------------------------------------------------
Frank's Profile: http://www.excelforum.com/member.php...nfo&userid=464
View this thread: http://www.excelforum.com/showthread...hreadid=497848

Hi Frank,
One possible reason could be invisible character(s) in the first column
of the lookup table
eg say =VLOOKUP(3,A1:H10,2,FALSE) and the "3" in A1:A10 is actually "
3" or "3 " then VLOOKUP returns #N/A because a clean 3 could not be
found and if you left out the last argument (FALSE) or used TRUE then
VLOOKUP will return an incorrect value because it would have used the
value in A1:H10 that is closest to being equal to 3.
Ken Johnson

"Frank" wrote in
message ...

Why is it that often this formula doesn't work?
Do I need to clean up cells, or do they need to be in the same format?


Frank

What do you mean by "doesn't work"? What does happen? Do you get an error or
an unexpected result?
Try posting the formula you are using (or an example).

The data does need to be of the same format as the lookup value. So, for
example, the number 123 is different from the text string "123".

Also, in text strings, spaces are important but may not show up easily. For
example, "Cats and dogs" is different from "Cats and dogs" (the latter
having two spaces between the first two words).

Have you looked at the definition of VLOOKUP in Help?
VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)
The last (optional) parameter, range_lookup, determines whether you require
an exact or approximate match. If you specify FALSE, only exact matches will
"work". However, if you specify approximate (the default, if parameter
range_lookup is omitted, or TRUE), the data must be sorted in ascending
order. This has potential for giving confusing results.