VLOOKUP returns #N/A

[magmike]

=VLOOKUP(H2,'Lookup Table'!$A$2:$B$110,2,FALSE)

I know the problem is in my reference to H2. The cell is a ZIP Code.

Any help is appreciated.

Magmike

 

[GS]

=VLOOKUP(H2,'Lookup Table'!$A$2:$B$110,2,FALSE)

I know the problem is in my reference to H2. The cell is a ZIP Code.

Any help is appreciated.

Magmike

The function expects an exact match. You can wrap this in an IF
construct to return an empty string on error, the reult on success.
Something like...

=IF(ISERROR(VLOOKUP(H2,'Lookup
Table'!$A$2:$B$110,2,FALSE)),"",VLOOKUP(H2,'Lookup
Table'!$A$2:$B$110,2,FALSE))

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[lhkittle]

=VLOOKUP(H2,'Lookup Table'!$A$2:$B$110,2,FALSE)



I know the problem is in my reference to H2. The cell is a ZIP Code.



Any help is appreciated.



Magmike

The formula works for me.

What is it about the reference to H2 that makes you sure that is the problem?

Regards,
Howard

 

[GS]

The formula works for me.

What is it about the reference to H2 that makes you sure that is the problem?

Regards,
Howard

I'm guessing H2 contains non-visible chars not in the lookup list, OR
vice-versa!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[magmike]

When I replaced the reference (H2) with an actual value (37185) it worked.

 

[GS]

When I replaced the reference (H2) with an actual value (37185) it worked.

Your formula works for me as expected! My revised version of your
formula also works. Nothing wrong with the ref to H2, IMO!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[joeu2004]

=VLOOKUP(H2,'Lookup Table'!$A$2:$B$110,2,FALSE)
I know the problem is in my reference to H2. The cell is a ZIP Code.

When I replaced the reference (H2) with an actual value (37185) it worked.

Then the cell H2 probably contains (numeric) text instead of a number. That
is, ISTEXT(H2) returns TRUE. (Confirm.)

A quick fix might be:

=VLOOKUP(--H2,'Lookup Table'!$A$2:$B$110,2,FALSE)

The double-negate (--) before H2 converts numeric text to a number.

If that returns an error (probably #VALUE), there are other characters in H2
that prevent Excel from interpreting it as a number. Try:

=VLOOKUP(--TRIM(SUBSTITUTE(H2,CHAR(160),"")),'Lookup
Table'!$A$2:$B$110,2,FALSE)

These are all short-term fixes. You should ask for help with a long-term
fix. For that, we will need more information. For example, how did you
enter the data into H2 (and others?) in the first place.

 

[magmike]

"magmike" wrote: > =VLOOKUP(H2,'Lookup Table'!$A$2:$B$110,2,FALSE) > I know the problem is in my reference to H2. The cell is a ZIP Code. "magmike" <[email protected]> wrote: > When I replaced the reference (H2) with an actual value (37185) it worked. Then the cell H2 probably contains (numeric) text instead of a number. That is, ISTEXT(H2) returns TRUE. (Confirm.) A quick fix might be: =VLOOKUP(--H2,'Lookup Table'!$A$2:$B$110,2,FALSE) Thedouble-negate (--) before H2 converts numeric text to a number. If that returns an error (probably #VALUE), there are other characters in H2 that prevent Excel from interpreting it as a number. Try: =VLOOKUP(--TRIM(SUBSTITUTE(H2,CHAR(160),"")),'Lookup Table'!$A$2:$B$110,2,FALSE) These are all short-term fixes. You should ask for help with a long-term fix. For that, we will need more information. For example, how did you enter the data into H2 (and others?) in the first place.

I'm not sure how my boss in her the data, if by hand, cut-and-paste or he received the spreadsheet arty entered from someone else. But tables were formatted as "general". I converted them to number format, but that doesn't correct the error. The double negative does.

 

[magmike]

I am not certain how my boss entered this data – whether by hand, cut-and-paste or if he received the spreadsheet from someone else. But all the cells in both tables were formatted as "general". I I changed the cell format to numbers, but that didn't fix the outcome. And double negative did. What is the long-term fix?

magmike

 

[Claus Busch]

Hi,

Am Sun, 18 Aug 2013 07:29:32 -0700 (PDT) schrieb magmike:

I am not certain how my boss entered this data ? whether by hand, cut-and-paste or if he received the spreadsheet from someone else. But all the cells in both tables were formatted as "general". I I changed the cell format to numbers, but that didn't fix the outcome. And double negative did. What is the long-term fix?

write a 1 in a empty cell => copy this cell => select your data => Paste
special => Multiply
Or format your data as General => select the column => Data =>
TextToColumns => Fixed Width => Finish


Regards
Claus B.