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

[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.

 

[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)

 

[Alojz]

What about posting some sample data and ur formula syntax?

 

[Alojz]

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

 

[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.

 

[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)

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.

 

[Tickfarmer]

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.

 

[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

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.

 

[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

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.

 

[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))

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.

 

[Tickfarmer]

Thank You!
This worked perfectly.
--
Tickfarmer

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))

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.

 

[Alojz]

U r welcome and thanks for feedback.

Thank You!
This worked perfectly.
--
Tickfarmer

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))

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.

 

[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:

 

[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 -