|
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
VLOOKUP Returns #NA For text/numbers with a dash -
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 |
| All times are GMT +1. The time now is 04:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com