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.
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
- Thread starter mpadgett
- Start date
- Thread starter
- #2
- Thread starter
- #3
-
1
- #4
AnotherHiggins
Technical User
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.
- 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.
- Thread starter
- #5
- Status