Vlookup and #N/A

[kmap]

I have done plenty of vlookups in the past, but all of the sudden I am
getting stuck. I don't know what else to verify in order to correct the
issue. I am receiving an #N/A. I verified that the information is sorted
appropriately, there is actually information in the referenced cell, and I
have checked the formula. I'm not sure what else to check. Don't know if it
makes a different, but the worksheet it should be pulling from was created by
another inidividual and pasted in. Any suggestions?
Thanks!
K
Example: =VLOOKUP(A2,Lite,2,FALSE)

 

[Dave Peterson]

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

If this doesn't help, you may want to share some of the data (and types of data)
that you're matching on--is it a number or text, a long string, date or time or
combination???

 

[Niek Otten]

Maybe this....

=================================================================
Your Numbers don’t behave (like numbers)
Niek Otten, May 11, 2006

Your numbers sort incorrectly, are not included in SUMs, cause #VALUE!
results in formulas, cannot be found in LOOKUPs, etc. In short:

Your Numbers look like Numbers, but they really are Text.
Sure! You formatted them as numbers, but alas, formatting afterwards doesn’t
help… Believe me, they are Text!

Here’s a checklist which will help you solve most known cases. Make a copy
of your workbook before trying! Always use Excel’s ISNUMBER() function to
check your cells; maybe you solved your problem in the first step!

· Format an empty cell as Number. Enter the number 1 in it.
Edit>Copy.
Select your “numbersâ€. Edit>Paste Special, check Multiply. Hopefully your
cells are “real†Numbers now
· If that doesn’t help, there may be spaces in your “numbersâ€. You
can use the LEN() function to compare the number of characters that Excel
sees in the cell with the number of characters you see. If you suspect
spaces, use Excel’s TRIM() function to remove them
· If that doesn’t help, there may be nonprintable characters in your
“numbersâ€. You can use Excel’s CLEAN() function to remove most of them
· If that doesn’t help, there may be non-breaking spaces in your
“numbers†(mostly acquired from Web Pages). Use David McRitchie’s TRIMALL()
function to remove them. It can be downloaded here:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

=================================================================

 

[Thanks]

Have you verified that there are no leading or trailing spaces in the
contents of the pasted values?

 

[T. Valko]

See this:

http://contextures.com/xlFunctions02.html#Trouble

 

[JP Ronse]

Hi,

When you use false in vlookup then there is no need to have a sorted
lookuprange.
2 things I can think off:
- Is Lite defined as the lookuprange?
- Perhaps you may have unvisible characters like space in the lookupvalue or
lookuprange.


Wkr,

JP

 

[kmap]

Yes, I believe this may be it. Is there a easy way to delete trailing spaces
after data. There are about 3 spaces after the data, I'm dealing with about
2000 rows...any quick fix? Thanks much!
K

 

[JP Ronse]

Hi K

Try Edit, Replace

Find <space>
Replace 'nothing'

Wkr,

JP

 

[Sean Timmons]

Ctrl+H find <three spaces> Replace With: <leave blank>

 

[Dave Peterson]

If you have leading spaces you want to get rid of, too, you can use this
technique:

select the column to fix
data|text to columns (xl2003 menus)
Fixed width (but remove any lines that excel added!)
and finish up.

The leading and trailing spaces will be removed, but any multiple internal
spaces will be kept.

 

[AwlSome Auger]

I have done plenty of vlookups in the past, but all of the sudden I am
getting stuck. I don't know what else to verify in order to correct the
issue. I am receiving an #N/A. I verified that the information is sorted
appropriately, there is actually information in the referenced cell, and I
have checked the formula. I'm not sure what else to check. Don't know if it
makes a different, but the worksheet it should be pulling from was created by
another inidividual and pasted in. Any suggestions?
Thanks!
K
Example: =VLOOKUP(A2,Lite,2,FALSE)

The lookup reference in A2 is what gets hunted for in the "Lite" array.

If it does not find a match, the failure will be in the first section
of the vlookup function, the reference or 'Lookup_value'.

If it is in the table array, look at your name manager under formulas
tab to insure that your named range is still being called correctly.

Lastly, make sure that the "A2" source and the lookup are "General"
format.

You *may* (should) also have to hand re-enter data contained in a cell
after a format change.