vlookup returning #NA

[Guest]

I've tried searching, I've used contextures.com but couldn't find the right
help.
I have an extremely large table: A2:C49918
Column A = Zip Codes Column B = State Column C = County
What I want is the state and county to pull when someone enters a zip in
field G6 of a different sheet. my formula for state is as follows:
=vlookup(G6,'Zip Code Lookup'!$A$2:$C$49918,2) and I am getting the
infamous #NA as a returned result after I enter a valid value into G6. any
suggestions. once I get this formula to work, I can use it to pull the county
as well.
and yes, I have formatted the values to 'Numbers' rather than 'Text'
Thanks in advance

 

[Fredrik Wahlgren]

I've tried searching, I've used contextures.com but couldn't find the right
help.
I have an extremely large table: A2:C49918
Column A = Zip Codes Column B = State Column C = County
What I want is the state and county to pull when someone enters a zip in
field G6 of a different sheet. my formula for state is as follows:
=vlookup(G6,'Zip Code Lookup'!$A$2:$C$49918,2) and I am getting the
infamous #NA as a returned result after I enter a valid value into G6. any
suggestions. once I get this formula to work, I can use it to pull the county
as well.
and yes, I have formatted the values to 'Numbers' rather than 'Text'
Thanks in advance

If G6 is in a different sheet, you need something like this
=vlookup(SheetInQuestion!G6,'Zip Code Lookup'!$A$2:$C$49918,2)

/Fredrik

 

[Guest]

I'm sorry, I should have clarified: the vlookup formula is going in H6,
directly next to the G6 on the same page. the table is on a different page:
'Zip Code Lookup'

 

[Fredrik Wahlgren]

I'm sorry, I should have clarified: the vlookup formula is going in H6,
directly next to the G6 on the same page. the table is on a different page:
'Zip Code Lookup'

Is 'Zip Code Lookup' a named range that refers to the correct range. Are you
sure it refers to the range in the correct worksheet? I assume that's page
means. Unfortunately, I haven't used VLOOKUP veryoften.

/Fredrik

 

[Guest]

Did you try using the false identifier? If the zip codes aren't in numerical
order I'm not sure if it would find it. Try
=vlookup(G6,'Zip Code Lookup'!$A$2:$C$49918,2,false)
see if that works.

 

[Guest]

does the vlookup have to be on the same page as the table?

 

[Guest]

No. Vlookup can pull information from a completely seperate file if you want
it to.

 

[CLR]

No, but if not, you must refer to the SheetName as part of the Range in the
formula......better/easier IMHO to give a RangeName to the table.........(
Insert > Name > Define..... ).........then use a formula such as something
like this:

=VLOOKUP(G6,MYTABLE,2,FALSE)

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Did you rename sheet2 to be Zip Code Lookup?

 

[Guest]

I GOT IT!!!

I formatted G6 (entry field) as Text, and all the zip codes as General. For
some reason, that worked and the formula is now giving me the state for the
zip code entered.
Thanks for all the help guys!! weird

 

[Dave Peterson]

It's more than formatting.

The values in the cells have to match.

If you have a cell formatted as General and type 12345 in that cell, then later
format the cell as text, the value in that cell will still be a number (until
you edit that cell).

If your lookup table has its key values as text, you can use:
=vlookup(text(g6,"00000"),.....

To match text with text.

If your lookup table has its key values as numbers, you can use:
=vlookup(--g6,....)

the -- converts the text value to numbers.

=======
Personally, I'd choose a format (number or text) and make sure my data matched
in both spots.

If you want to convert those text numbers to numeric numbers (huh?), you can do
this:

Copy an empty cell.
select your column
edit|paste special|check Add

or

Select your column
data|text to columns|finish.

 

[Dave Peterson]

Oops. Didn't see your post that you found a solution.