VLOOKUP Returns #NA For text/numbers with a dash -

T

Tickfarmer

Working with 2 spreadsheets - trying to look up by value A1 (Item number).
Many of the rows work correctly...however for item numbers that contain a
dash (-) (Example: 10050-4) the result is #NA. I have verified that the item
number is on both worksheets.

I have tried converting the item to text and numeric, but all entries that
include a dash - return #NA.

Please help.
 
S

Sheeloo

Problem is not with dash.

It must be the format or extra spaces

Test both with ISTEXT..
Check LEN

Remove all formats from the lookup cell
Copy the value from the cell in VLOOKUP there and see

I pasted 10050-4 in A1 and H1 and entered TEST in I1
Following formula in B1 returned TEST as expected
=VLOOKUP(A1,H:I,2,FALSE)
 
T

Tickfarmer

This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

for example item number 6901-3 is listed in both worksheets and has a qty to
return. However it returns #na. The qty to return has been formatted to
general, text and number, as has the item number. none of the different
formats work. I also have the same problem with numbers who do not have a
dash. Can I email you the spreadsheets to take a look.
 
A

Alojz

Formula looks correct. Could u check links are open, both files are in the
same directory? If still problem, make both files as small as possible, but
still giving error, and send it to (e-mail address removed)

Tickfarmer said:
This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

for example item number 6901-3 is listed in both worksheets and has a qty to
return. However it returns #na. The qty to return has been formatted to
general, text and number, as has the item number. none of the different
formats work. I also have the same problem with numbers who do not have a
dash. Can I email you the spreadsheets to take a look.
--
Tickfarmer


Alojz said:
Hi Sheeloo, u r correct dash definitily does not create problem in vlookup.
 
T

Tickfarmer

Sent small "sample" file as example now.
--
Tickfarmer


Alojz said:
Formula looks correct. Could u check links are open, both files are in the
same directory? If still problem, make both files as small as possible, but
still giving error, and send it to (e-mail address removed)

Tickfarmer said:
This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

for example item number 6901-3 is listed in both worksheets and has a qty to
return. However it returns #na. The qty to return has been formatted to
general, text and number, as has the item number. none of the different
formats work. I also have the same problem with numbers who do not have a
dash. Can I email you the spreadsheets to take a look.
--
Tickfarmer


Alojz said:
Hi Sheeloo, u r correct dash definitily does not create problem in vlookup.

:

Problem is not with dash.

It must be the format or extra spaces

Test both with ISTEXT..
Check LEN

Remove all formats from the lookup cell
Copy the value from the cell in VLOOKUP there and see

I pasted 10050-4 in A1 and H1 and entered TEST in I1
Following formula in B1 returned TEST as expected
=VLOOKUP(A1,H:I,2,FALSE)


:

Working with 2 spreadsheets - trying to look up by value A1 (Item number).
Many of the rows work correctly...however for item numbers that contain a
dash (-) (Example: 10050-4) the result is #NA. I have verified that the item
number is on both worksheets.

I have tried converting the item to text and numeric, but all entries that
include a dash - return #NA.

Please help.
 
A

Alojz

Hi, data in two sheets are not the same. Items in col A Pulling from B sheet
have space after them, click in A2, press F2, left arrow. Can u see it? U
have to get rid of space following characters in cells in A col of second
sheet, then formula will work. Or else, change formula to:
=VLOOKUP(A1070&" ",[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

HTH, click yes, if so.
Regards,
Alojz

Tickfarmer said:
Sent small "sample" file as example now.
--
Tickfarmer


Alojz said:
Formula looks correct. Could u check links are open, both files are in the
same directory? If still problem, make both files as small as possible, but
still giving error, and send it to (e-mail address removed)

Tickfarmer said:
This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

for example item number 6901-3 is listed in both worksheets and has a qty to
return. However it returns #na. The qty to return has been formatted to
general, text and number, as has the item number. none of the different
formats work. I also have the same problem with numbers who do not have a
dash. Can I email you the spreadsheets to take a look.
--
Tickfarmer


:

Hi Sheeloo, u r correct dash definitily does not create problem in vlookup.

:

Problem is not with dash.

It must be the format or extra spaces

Test both with ISTEXT..
Check LEN

Remove all formats from the lookup cell
Copy the value from the cell in VLOOKUP there and see

I pasted 10050-4 in A1 and H1 and entered TEST in I1
Following formula in B1 returned TEST as expected
=VLOOKUP(A1,H:I,2,FALSE)


:

Working with 2 spreadsheets - trying to look up by value A1 (Item number).
Many of the rows work correctly...however for item numbers that contain a
dash (-) (Example: 10050-4) the result is #NA. I have verified that the item
number is on both worksheets.

I have tried converting the item to text and numeric, but all entries that
include a dash - return #NA.

Please help.
 
A

Alojz

Oops, in sample file, there are only A&B col on second sheet, then, to be
correct, formula is: =VLOOKUP(A1070&"
",[Test_ShippedPO2.xls]Sheet1!$A:$B,2,0).
U did the file really small :)

Alojz said:
Hi, data in two sheets are not the same. Items in col A Pulling from B sheet
have space after them, click in A2, press F2, left arrow. Can u see it? U
have to get rid of space following characters in cells in A col of second
sheet, then formula will work. Or else, change formula to:
=VLOOKUP(A1070&" ",[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

HTH, click yes, if so.
Regards,
Alojz

Tickfarmer said:
Sent small "sample" file as example now.
--
Tickfarmer


Alojz said:
Formula looks correct. Could u check links are open, both files are in the
same directory? If still problem, make both files as small as possible, but
still giving error, and send it to (e-mail address removed)

:

This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

for example item number 6901-3 is listed in both worksheets and has a qty to
return. However it returns #na. The qty to return has been formatted to
general, text and number, as has the item number. none of the different
formats work. I also have the same problem with numbers who do not have a
dash. Can I email you the spreadsheets to take a look.
--
Tickfarmer


:

Hi Sheeloo, u r correct dash definitily does not create problem in vlookup.

:

Problem is not with dash.

It must be the format or extra spaces

Test both with ISTEXT..
Check LEN

Remove all formats from the lookup cell
Copy the value from the cell in VLOOKUP there and see

I pasted 10050-4 in A1 and H1 and entered TEST in I1
Following formula in B1 returned TEST as expected
=VLOOKUP(A1,H:I,2,FALSE)


:

Working with 2 spreadsheets - trying to look up by value A1 (Item number).
Many of the rows work correctly...however for item numbers that contain a
dash (-) (Example: 10050-4) the result is #NA. I have verified that the item
number is on both worksheets.

I have tried converting the item to text and numeric, but all entries that
include a dash - return #NA.

Please help.
 
A

Alojz

One more remark: adding the space to VLOOKUP formula may spoil it for other
cases where it worked so far. So, u can build it more sofisticated, like:
=IF(ISNA(VLOOKUP(A2,'Pulling from Column B'!A:B,2,0)),VLOOKUP(A2&"
",'Pulling from Column B'!A:B,2,0),VLOOKUP(A2,'Pulling from Column
B'!A:B,2,0))


Alojz said:
Oops, in sample file, there are only A&B col on second sheet, then, to be
correct, formula is: =VLOOKUP(A1070&"
",[Test_ShippedPO2.xls]Sheet1!$A:$B,2,0).
U did the file really small :)

Alojz said:
Hi, data in two sheets are not the same. Items in col A Pulling from B sheet
have space after them, click in A2, press F2, left arrow. Can u see it? U
have to get rid of space following characters in cells in A col of second
sheet, then formula will work. Or else, change formula to:
=VLOOKUP(A1070&" ",[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

HTH, click yes, if so.
Regards,
Alojz

Tickfarmer said:
Sent small "sample" file as example now.
--
Tickfarmer


:

Formula looks correct. Could u check links are open, both files are in the
same directory? If still problem, make both files as small as possible, but
still giving error, and send it to (e-mail address removed)

:

This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

for example item number 6901-3 is listed in both worksheets and has a qty to
return. However it returns #na. The qty to return has been formatted to
general, text and number, as has the item number. none of the different
formats work. I also have the same problem with numbers who do not have a
dash. Can I email you the spreadsheets to take a look.
--
Tickfarmer


:

Hi Sheeloo, u r correct dash definitily does not create problem in vlookup.

:

Problem is not with dash.

It must be the format or extra spaces

Test both with ISTEXT..
Check LEN

Remove all formats from the lookup cell
Copy the value from the cell in VLOOKUP there and see

I pasted 10050-4 in A1 and H1 and entered TEST in I1
Following formula in B1 returned TEST as expected
=VLOOKUP(A1,H:I,2,FALSE)


:

Working with 2 spreadsheets - trying to look up by value A1 (Item number).
Many of the rows work correctly...however for item numbers that contain a
dash (-) (Example: 10050-4) the result is #NA. I have verified that the item
number is on both worksheets.

I have tried converting the item to text and numeric, but all entries that
include a dash - return #NA.

Please help.
 
T

Tickfarmer

Thank You!
This worked perfectly.
--
Tickfarmer


Alojz said:
One more remark: adding the space to VLOOKUP formula may spoil it for other
cases where it worked so far. So, u can build it more sofisticated, like:
=IF(ISNA(VLOOKUP(A2,'Pulling from Column B'!A:B,2,0)),VLOOKUP(A2&"
",'Pulling from Column B'!A:B,2,0),VLOOKUP(A2,'Pulling from Column
B'!A:B,2,0))


Alojz said:
Oops, in sample file, there are only A&B col on second sheet, then, to be
correct, formula is: =VLOOKUP(A1070&"
",[Test_ShippedPO2.xls]Sheet1!$A:$B,2,0).
U did the file really small :)

Alojz said:
Hi, data in two sheets are not the same. Items in col A Pulling from B sheet
have space after them, click in A2, press F2, left arrow. Can u see it? U
have to get rid of space following characters in cells in A col of second
sheet, then formula will work. Or else, change formula to:
=VLOOKUP(A1070&" ",[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

HTH, click yes, if so.
Regards,
Alojz

:

Sent small "sample" file as example now.
--
Tickfarmer


:

Formula looks correct. Could u check links are open, both files are in the
same directory? If still problem, make both files as small as possible, but
still giving error, and send it to (e-mail address removed)

:

This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

for example item number 6901-3 is listed in both worksheets and has a qty to
return. However it returns #na. The qty to return has been formatted to
general, text and number, as has the item number. none of the different
formats work. I also have the same problem with numbers who do not have a
dash. Can I email you the spreadsheets to take a look.
--
Tickfarmer


:

Hi Sheeloo, u r correct dash definitily does not create problem in vlookup.

:

Problem is not with dash.

It must be the format or extra spaces

Test both with ISTEXT..
Check LEN

Remove all formats from the lookup cell
Copy the value from the cell in VLOOKUP there and see

I pasted 10050-4 in A1 and H1 and entered TEST in I1
Following formula in B1 returned TEST as expected
=VLOOKUP(A1,H:I,2,FALSE)


:

Working with 2 spreadsheets - trying to look up by value A1 (Item number).
Many of the rows work correctly...however for item numbers that contain a
dash (-) (Example: 10050-4) the result is #NA. I have verified that the item
number is on both worksheets.

I have tried converting the item to text and numeric, but all entries that
include a dash - return #NA.

Please help.
 
A

Alojz

U r welcome and thanks for feedback.

Tickfarmer said:
Thank You!
This worked perfectly.
--
Tickfarmer


Alojz said:
One more remark: adding the space to VLOOKUP formula may spoil it for other
cases where it worked so far. So, u can build it more sofisticated, like:
=IF(ISNA(VLOOKUP(A2,'Pulling from Column B'!A:B,2,0)),VLOOKUP(A2&"
",'Pulling from Column B'!A:B,2,0),VLOOKUP(A2,'Pulling from Column
B'!A:B,2,0))


Alojz said:
Oops, in sample file, there are only A&B col on second sheet, then, to be
correct, formula is: =VLOOKUP(A1070&"
",[Test_ShippedPO2.xls]Sheet1!$A:$B,2,0).
U did the file really small :)

:

Hi, data in two sheets are not the same. Items in col A Pulling from B sheet
have space after them, click in A2, press F2, left arrow. Can u see it? U
have to get rid of space following characters in cells in A col of second
sheet, then formula will work. Or else, change formula to:
=VLOOKUP(A1070&" ",[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

HTH, click yes, if so.
Regards,
Alojz

:

Sent small "sample" file as example now.
--
Tickfarmer


:

Formula looks correct. Could u check links are open, both files are in the
same directory? If still problem, make both files as small as possible, but
still giving error, and send it to (e-mail address removed)

:

This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

for example item number 6901-3 is listed in both worksheets and has a qty to
return. However it returns #na. The qty to return has been formatted to
general, text and number, as has the item number. none of the different
formats work. I also have the same problem with numbers who do not have a
dash. Can I email you the spreadsheets to take a look.
--
Tickfarmer


:

Hi Sheeloo, u r correct dash definitily does not create problem in vlookup.

:

Problem is not with dash.

It must be the format or extra spaces

Test both with ISTEXT..
Check LEN

Remove all formats from the lookup cell
Copy the value from the cell in VLOOKUP there and see

I pasted 10050-4 in A1 and H1 and entered TEST in I1
Following formula in B1 returned TEST as expected
=VLOOKUP(A1,H:I,2,FALSE)


:

Working with 2 spreadsheets - trying to look up by value A1 (Item number).
Many of the rows work correctly...however for item numbers that contain a
dash (-) (Example: 10050-4) the result is #NA. I have verified that the item
number is on both worksheets.

I have tried converting the item to text and numeric, but all entries that
include a dash - return #NA.

Please help.
 
A

ARTHUR HUMBERT

I am trying to ling to worksheet. The vlookup formula works for everything except one category 'wind'
=VLOOKUP($B39&$C39,BelgiumSummary,MATCH(D$1,Belgium2Heading,0),FALSE)
=VLOOKUP($B40&$C40,BelgiumSummary,MATCH(D$1,Belgium2Heading,0),FALSE)

This formula is the same in all my wroksheet for different country but it seems to stop working only for Wind which is B39, the C column is a subcategory.

Working with 2 spreadsheets - trying to look up by value A1 (Item number).
Many of the rows work correctly...however for item numbers that contain a
dash (-) (Example: 10050-4) the result is #NA. I have verified that the item
number is on both worksheets.

I have tried converting the item to text and numeric, but all entries that
include a dash - return #NA.

Please help.

--
Tickfarmer
On Tuesday, March 03, 2009 3:44 PM Click above to get my email id wrote:
Problem is not with dash.

It must be the format or extra spaces

Test both with ISTEXT..
Check LEN

Remove all formats from the lookup cell
Copy the value from the cell in VLOOKUP there and see

I pasted 10050-4 in A1 and H1 and entered TEST in I1
Following formula in B1 returned TEST as expected
=VLOOKUP(A1,H:I,2,FALSE)


"Tickfarmer" wrote:
On Wednesday, March 04, 2009 12:01 PM Tickfarme wrote:
This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

for example item number 6901-3 is listed in both worksheets and has a qty to
return. However it returns #na. The qty to return has been formatted to
general, text and number, as has the item number. none of the different
formats work. I also have the same problem with numbers who do not have a
dash. Can I email you the spreadsheets to take a look.
--
Tickfarmer


"Alojz" wrote:
On Wednesday, March 04, 2009 4:31 PM Aloj wrote:
Hi, data in two sheets are not the same. Items in col A Pulling from B sheet
have space after them, click in A2, press F2, left arrow. Can u see it? U
have to get rid of space following characters in cells in A col of second
sheet, then formula will work. Or else, change formula to:
=VLOOKUP(A1070&" ",[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

HTH, click yes, if so.
Regards,
Alojz

"Tickfarmer" wrote:
On Wednesday, March 04, 2009 4:39 PM Aloj wrote:
Oops, in sample file, there are only A&B col on second sheet, then, to be
correct, formula is: =VLOOKUP(A1070&"
",[Test_ShippedPO2.xls]Sheet1!$A:$B,2,0).
U did the file really small :)

"Alojz" wrote:
 
P

Pete_UK

Check the entry for Wind in B39 - it might actually be Wind<space>, so
you will not get an exact match on it. You can see if there are any
other characters by:

=LEN(B39)

which will tell you how many characters there are in B39.

Hope this helps.

Pete

I am trying to ling to worksheet. The vlookup formula works for everything except one category 'wind'
=VLOOKUP($B39&$C39,BelgiumSummary,MATCH(D$1,Belgium2Heading,0),FALSE)
=VLOOKUP($B40&$C40,BelgiumSummary,MATCH(D$1,Belgium2Heading,0),FALSE)

This formula is the same in all my wroksheet for different country but itseems to stop working only for Wind which is B39, the C column is a subcategory.


On Tuesday, March 03, 2009 3:35 PM Tickfarme wrote:
Working with 2 spreadsheets - trying to look up by value A1 (Item number).  
Many of the rows work correctly...however for item numbers that containa
dash (-) (Example: 10050-4) the result is #NA.  I have verified that the item
number is on both worksheets.
I have tried converting the item to text and numeric, but all entries that
include a dash - return #NA.
Please help.
--
Tickfarmer
On Tuesday, March 03, 2009 3:44 PM Click above to get my email id wrote:
Problem is not with dash.
It must be the format or extra spaces
Test both with ISTEXT..
Check LEN
Remove all formats from the lookup cell
Copy the value from the cell in VLOOKUP there and see
I pasted 10050-4 in A1 and H1 and entered TEST in I1
Following formula in B1 returned TEST as expected
=VLOOKUP(A1,H:I,2,FALSE)
:
On Tuesday, March 03, 2009 3:46 PM Aloj wrote:
What about posting some sample data and ur formula syntax?
:
On Tuesday, March 03, 2009 4:14 PM Aloj wrote:
Hi Sheeloo, u r correct dash definitily does not create problem in vlookup.
:
On Wednesday, March 04, 2009 12:01 PM Tickfarme wrote:
This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)
for example item number 6901-3 is listed in both worksheets and hasa qty to
return.  However it returns #na.  The qty to return has been formatted to
general, text and number, as has the item number.  none of the different
formats work.  I also have the same problem with numbers who do not have a
dash.  Can I email you the spreadsheets to take a look.
--
Tickfarmer
:
On Wednesday, March 04, 2009 2:51 PM Aloj wrote:
Formula looks correct. Could u check links are open, both files are in the
same directory? If still problem, make both files as small as possible, but
still giving error, and send it to (e-mail address removed)
:
On Wednesday, March 04, 2009 3:09 PM Tickfarme wrote:
Sent small "sample" file as example now.
--
Tickfarmer
:
On Wednesday, March 04, 2009 4:31 PM Aloj wrote:
Hi, data in two sheets are not the same. Items in col A Pulling from B sheet
have space after them, click in A2, press F2, left arrow. Can u see it? U
have to get rid of space following characters in cells in A col of second
sheet, then formula will work. Or else, change formula to:
=VLOOKUP(A1070&" ",[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)
HTH, click yes, if so.
Regards,
Alojz
:
On Wednesday, March 04, 2009 4:39 PM Aloj wrote:
Oops, in sample file, there are only A&B col on second sheet, then, to be
correct, formula is: =VLOOKUP(A1070&"
",[Test_ShippedPO2.xls]Sheet1!$A:$B,2,0).
U did the file really small :)
:
adding the space to VLOOKUP formula may spoilit for other
cases where it worked so far. So, u can build it more sofisticated, like:
=IF(ISNA(VLOOKUP(A2,'Pulling from Column B'!A:B,2,0)),VLOOKUP(A2&"
",'Pulling from Column B'!A:B,2,0),VLOOKUP(A2,'Pulling from Column
B'!A:B,2,0))
:
On Wednesday, March 04, 2009 5:56 PM Tickfarme wrote:
Thank You!
This worked perfectly.
--
Tickfarmer
:
On Wednesday, March 04, 2009 6:57 PM Aloj wrote:
U r welcome and thanks for feedback.
:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Make The WebClient Class follow redirects and get Target Url
http://www.eggheadcafe.com/tutorials/aspnet/70511872-c3aa-4e92-a7d7-d...- Hide quoted text -

- Show quoted text -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top