vlookup with text data

jcmeyer

New Member
Joined
Mar 26, 2002
Messages
6
I am having a problem using text data with the vlookup function. The lookup value is in text format and the first column of the data range is in text format. If I copy and paste the lookup value into the lookup value cell location, I get #N/A as a result of my vlookup function, even though the lookup value is in the first column of my data range.

If I type the lookup value into the the lookup value cell location, the function works fine. This isn't a solution that I can use, because I have a list of items to lookup and it would take too long to copy and paste the values and retype these values in the same cell.

Has anyone encountered this issue before?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
On 2002-03-27 09:18, jcmeyer wrote:
I am having a problem using text data with the vlookup function. The lookup value is in text format and the first column of the data range is in text format. If I copy and paste the lookup value into the lookup value cell location, I get #N/A as a result of my vlookup function, even though the lookup value is in the first column of my data range.

If I type the lookup value into the the lookup value cell location, the function works fine. This isn't a solution that I can use, because I have a list of items to lookup and it would take too long to copy and paste the values and retype these values in the same cell.

Has anyone encountered this issue before?

what are the results of the =ISNUMBER()
formula, using the first value in your look up table, and the value you want to look up?

If they are both text, the results should both be false. A real "number" can reside in a cell formatted as text.
 
Upvote 0
The rsults of both isnumber() formulas is false. Does that mean the vlookup function won't work for this data?
 
Upvote 0
On 2002-03-27 09:33, jcmeyer wrote:
The rsults of both isnumber() formulas is false. Does that mean the vlookup function won't work for this data?

Check also whether the lookup value and the same value in the table do have the same length:

=LEN(lookup-value)=LEN(the-same-entry-in-the-first-col-of-the-lookup-table)

because of extraneous spaces.
 
Upvote 0
Hi jemeyer:
How about if you post some of your sample data, and the formula you are using -- that will be a big help in looking at what you are working with and where is the problem. It appears you may have a syntax problem in using the VLOOKUP function!
 
Upvote 0
On 2002-03-27 09:41, jcmeyer wrote:
The lengths of both values are the same.

=COUNTIF(A1:A10,lookup-value)

where A1:A10 is the first column range of your lookup-table.

What do you get?

As Anand suggests, post also your VLOOKUP formula in the follow-up.
 
Upvote 0
On 2002-03-27 09:53, jcmeyer wrote:
Yogi,

Would you rather I sent you the file by email. It is not a very big file.

Thanks,

John

Hi jcmeyer:
Yes you can email it to me -- if you like -- or post it on the board!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top