vlookup returning #NA

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
dandigger said:
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
Click to expand...

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

/Fredrik
 
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'
 
dandigger said:
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'
Click to expand...

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
 
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.
 
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
 
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
 
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.
 
Back
Top