VLOOKUP seems correct, still getting #N/A
Here's the scenario:
1 workbook 2 sheets one sheet is a list of ship to numbers and their corresponding zip codes one sheet is the order sheet currently containing only the ship to numbers I am writing the VLOOKUP to refer back to the zip codes table to extract the zip code corresponding to the correct ship to # on the order sheet. My formula reads like this in words instead of cell references for the point of demonstration: =VLOOKUP(ShipToNumber, 'ZipCodeSheet'!ZipCodesTableRange, ColumnNumberContaingZipCodes,FALSE) Actual example: =VLOOKUP(G4,'Zip Codes'!A4:B6558,2,FALSE) Although the false statement doesn't make ascending sort necessary, the Zip Codes sheet is already sorted that way. The argument looks good, the logic seems correct - I have the exact shipto value, I gave the table range, I named the column for the zip codes, and I want an exact match. I tried this on multiple examples, but fails each time. Is it my syntax? I have been over and over this with an Excel Book on Formulas and the help files and I just cannot figure out why it isn't working. I even made sure that the zipcodes table's cells were all formatted with numbers. I hope that example is clearly stated, cause after 3 days, I am at a loss. Thanks for any help anyone could offer. Jazz |
At a guess - on one sheet the Ship To numbers are numeric & on the other they
are text. You can try =VLOOKUP(value(G4),'Zip Codes'!A4:B6558,2,FALSE) and if you still get the #NA try =VLOOKUP(Text(G4,"#"),'Zip Codes'!A4:B6558,2,FALSE) If neither works, post back "Jazz" wrote: Here's the scenario: 1 workbook 2 sheets one sheet is a list of ship to numbers and their corresponding zip codes one sheet is the order sheet currently containing only the ship to numbers I am writing the VLOOKUP to refer back to the zip codes table to extract the zip code corresponding to the correct ship to # on the order sheet. My formula reads like this in words instead of cell references for the point of demonstration: =VLOOKUP(ShipToNumber, 'ZipCodeSheet'!ZipCodesTableRange, ColumnNumberContaingZipCodes,FALSE) Actual example: =VLOOKUP(G4,'Zip Codes'!A4:B6558,2,FALSE) Although the false statement doesn't make ascending sort necessary, the Zip Codes sheet is already sorted that way. The argument looks good, the logic seems correct - I have the exact shipto value, I gave the table range, I named the column for the zip codes, and I want an exact match. I tried this on multiple examples, but fails each time. Is it my syntax? I have been over and over this with an Excel Book on Formulas and the help files and I just cannot figure out why it isn't working. I even made sure that the zipcodes table's cells were all formatted with numbers. I hope that example is clearly stated, cause after 3 days, I am at a loss. Thanks for any help anyone could offer. Jazz |
Oh- thank you thank you thank you thank you thank you
the first one worked - and yes, I understand why - again many thanks!!!!! "Duke Carey" wrote: At a guess - on one sheet the Ship To numbers are numeric & on the other they are text. You can try =VLOOKUP(value(G4),'Zip Codes'!A4:B6558,2,FALSE) and if you still get the #NA try =VLOOKUP(Text(G4,"#"),'Zip Codes'!A4:B6558,2,FALSE) If neither works, post back "Jazz" wrote: Here's the scenario: 1 workbook 2 sheets one sheet is a list of ship to numbers and their corresponding zip codes one sheet is the order sheet currently containing only the ship to numbers I am writing the VLOOKUP to refer back to the zip codes table to extract the zip code corresponding to the correct ship to # on the order sheet. My formula reads like this in words instead of cell references for the point of demonstration: =VLOOKUP(ShipToNumber, 'ZipCodeSheet'!ZipCodesTableRange, ColumnNumberContaingZipCodes,FALSE) Actual example: =VLOOKUP(G4,'Zip Codes'!A4:B6558,2,FALSE) Although the false statement doesn't make ascending sort necessary, the Zip Codes sheet is already sorted that way. The argument looks good, the logic seems correct - I have the exact shipto value, I gave the table range, I named the column for the zip codes, and I want an exact match. I tried this on multiple examples, but fails each time. Is it my syntax? I have been over and over this with an Excel Book on Formulas and the help files and I just cannot figure out why it isn't working. I even made sure that the zipcodes table's cells were all formatted with numbers. I hope that example is clearly stated, cause after 3 days, I am at a loss. Thanks for any help anyone could offer. Jazz |
All times are GMT +1. The time now is 01:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com