#N/A coming up in VLOOKUP when visibly the two cells are same

I have tried this a few times and tried to copy and paste but it still keeps
coming back with #N/A but if I double click in the cell that I am trying to
find it then finds it, hope this makes sense

Probably because the data within the lookup col in the vlookup's table_array
and the lookup values may not be consistent.

For example: If it's numbers being matched, these need to be consistent:
either match text numbers vs text numbers, or real numbers vs real numbers
(lookup values vs lookup col in table _array)

So instead of say:
=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

Try it as:
=VLOOKUP(A2+0,Sheet2!A:B,2,FALSE)
The "+0" is one way to coerce the text lookup number in A2 to a real number

Or, try:
=VLOOKUP(A2&"",Sheet2!A:B,2,FALSE)
to convert the real number in A2 to a text number
so that it will match the text numbers in the lookup col of the table_array

Or, try something like:
=VLOOKUP(TEXT(A2,"0000"),Sheet2!A:B,2,FALSE)
where there are leading zeros in the text numbers [to 4 digits]
in the lookup col of the table_array (eg: 0010, 0100, 0002, etc)
(Adapt the "0000" to suit the format in the table_array)

If it's text being matched, try:
=VLOOKUP(TRIM(A2),Sheet2!A:B,2,FALSE)
if there could be extraneous "invisible" whitespaces within the lookup value
in A2

Or, if its the other way around, try trimming col A in Sheet2 (the lookup
col in the table_array). In Sheet2, just place in say C1: =TRIM(A1), copy
down. Then cut-out col C and paste special as values to overwrite col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Marcus Bolton" wrote:
I have tried this a few times and tried to copy and paste but it still keeps
coming back with #N/A but if I double click in the cell that I am trying to
find it then finds it, hope this makes sense

Hi Marcus,

My standard answer:

================================================== =======
Vlookup gives wrong answer

Niek Otten, April 1 2006

Frequent causes:

1. Some cells look like numbers, but are actually text. You can check with the ISTEXT function.
Check both the search arguments and the lookup table.
Formatting as numbers afterwards doesn't help.
Remedy:
Format an empty cell as Number. Enter the number 1. EditCopy. Select your "numbers". EditPaste Special, check Multiply.

2. The data is not sorted ascending and the 4th argument of the VLOOKUP is TRUE or is omitted.

3. There are spaces or other invisible characters in either the search arguments or the lookup table.
This often happens when you import data from other applications.
Use the LEN() function to see how many characters there really are in the cell and compare that with what you see.
Use the TRIM function to remove all spaces except single spaces between words.
Use the CLEAN function to remove all nonprintable characters. HTML characters can be removed with a macro by David
McRitchie,
which can be downloaded he http://www.mvps.org/dmcritchie/excel/join.htm#trimall

4. The formula was copied from somewhere else, but the addresses of the lookup table were not absolute so have changed in
the Paste process
and now point to the wrong range.
Use absolute addresses for the lookup table, like $A$1:$B$20 instead of relative addresses like A1:B20.
When editing or entering a formula, use the F4 key to toggle between several forms of relative addresses and absolute ones
(normally only for the table, not for the search argument, but this depends on your specific problem).
The first hit of F4 changes the default relative address to an absolute one. That is usually what you need.
Even better: use a Defined Name for the table instead of cell addresses; InsertNameDefine.

5. The table was extended after its initial use, but the definition of the table in the VLOOKUP or in the Defined Name was
not adjusted accordingly.
To prevent this from happening: always use explicit bottom and top rows (with dummy error values if necessary)
and insert new rows or cells between those two.
Then the definition of the range or the Defined Name will adjust automatically.
Users of Excel 2003 and higher may profit from the List feature in the Data menu to maintain tables.
================================================== =======

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Marcus Bolton" Marcus wrote in message
...
|I have tried this a few times and tried to copy and paste but it still keeps
| coming back with #N/A but if I double click in the cell that I am trying to
| find it then finds it, hope this makes sense