VLOOKUP seems correct, still getting #N/A

[Jazz]

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

[Duke Carey]

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

[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

[bokusale]

Column A Column B Column C
Date NAME COUNTS
9/1/2011 Newport 5
9/1/2011 Newport Box 10




I put a formula as =VLOOKUP("Newport Box",A1:C4,3,false)
and I'm getting #N/A

Can someone help?

And well this is not a reply
This is my must post and I don't know where to post new

[zbor]

Wellcome bokusdale to the forum.
Please, read the rules first so you can ask your questions in propper way:

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.