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