Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

vlookup #N/A error 1

Status
Not open for further replies.

mpadgett

IS-IT--Management
Jun 24, 2005
57
US
I can't figure out why a vlookup formula will not work. I'm getting #N/A errors. I've used vlookup numerous times but never had this big of a challenge. It's almost like a data type problem. In the table_array, I have alpha-numeric data in column 1 and numeric data in column 2. I've changed the cell format in the table_array several times but can't get it to work. Does this sound familiar to anyone? Thanks.
 
In addition, if I preceed the data in column 1 of the table_array with a single quote (') the lookup works.
 
big culprits to look for:

- mixed data types, numbers and text
- leading/trailing spaces in cells

You already suspect the first, so let's run with it. Use the function =IsText to test an entry in both the reference cell and lookup table. If they are, indeed, differnt types of data that'll cause your problem.

You can either change the text to numbers or vice versa. A single tick mark in front of the number should force it to text. Alternately, you can force the numeric strings to numbers via the Times-one fix (search this forum for that term) or by changing the cell formats to general, then using Text-to-Columns, choosing no delimiters and changing the data type to number.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
The culprit was as you mentioned. Trailing spaces!

Thanks,
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top