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

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:

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

What about posting some sample data and ur formula syntax?

"Tickfarmer" 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 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

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

"Sheeloo" 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:

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

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:

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

"Sheeloo" 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:

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

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

"Tickfarmer" 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:

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

"Sheeloo" 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:

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

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


"Alojz" 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

"Tickfarmer" 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:

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

"Sheeloo" 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:

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

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:

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


"Alojz" 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

"Tickfarmer" 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:

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

"Sheeloo" 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:

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

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:

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:

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


"Alojz" 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

"Tickfarmer" 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:

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

"Sheeloo" 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:

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

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

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:

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


"Alojz" 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

"Tickfarmer" 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:

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

"Sheeloo" 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:

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


"Alojz" wrote:

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

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:

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


"Alojz" 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

"Tickfarmer" 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:

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

"Sheeloo" 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:

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

U r welcome and thanks for feedback.

"Tickfarmer" wrote:

Thank You!
This worked perfectly.
--
Tickfarmer


"Alojz" wrote:

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

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:

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


"Alojz" 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

"Tickfarmer" 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:

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

"Sheeloo" 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:

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