vlookup with text data

jcmeyer · Mar 27th, 2002, 10:18 AM

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?

IML · Mar 27th, 2002, 10:28 AM

Quote:

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.

jcmeyer · Mar 27th, 2002, 10:31 AM

jcmeyer · Mar 27th, 2002, 10:33 AM

The rsults of both isnumber() formulas is false. Does that mean the vlookup function won't work for this data?

Aladin Akyurek · Mar 27th, 2002, 10:38 AM

Quote:

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.

Yogi Anand · Mar 27th, 2002, 10:38 AM

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!

jcmeyer · Mar 27th, 2002, 10:41 AM

The lengths of both values are the same.

Aladin Akyurek · Mar 27th, 2002, 10:49 AM

Quote:

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.

jcmeyer · Mar 27th, 2002, 10:53 AM

Yogi,

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

Thanks,

John

Yogi Anand · Mar 27th, 2002, 10:59 AM

Quote:

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!

Mar 27th, 2002, 10:38 AM	#6
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	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! __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 10:18 AM	#1
jcmeyer New Member Join Date: Mar 2002 Posts: 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?

Mar 27th, 2002, 10:31 AM	#3
jcmeyer New Member Join Date: Mar 2002 Posts: 6

Mar 27th, 2002, 10:33 AM	#4
jcmeyer New Member Join Date: Mar 2002 Posts: 6	The rsults of both isnumber() formulas is false. Does that mean the vlookup function won't work for this data?

Mar 27th, 2002, 10:41 AM	#7
jcmeyer New Member Join Date: Mar 2002 Posts: 6	The lengths of both values are the same.

Mar 27th, 2002, 10:53 AM	#9
jcmeyer New Member Join Date: Mar 2002 Posts: 6	Yogi, Would you rather I sent you the file by email. It is not a very big file. Thanks, John