Need help with VLookup

[Don Wilkins]

I have an Excel sheet that I am using as a source file for a mailing
list. I enter First, Last, Street, City, State, and Zip. I would like
to have State and Zip entered automatically IF (and only IF) the City
I type is in the lookup list. The following works fine as long as the
city is in the list however if the city is not in the list V lookup
picks the nearest fit and enters it. This is not good.

The formula does enter a blank instead of #N/A if no city is entered
thus avoiding a string of #N/As in the columns.

Is there a way to modify my formula so if the city is not in the V
lookup list nothing is entered in the state and zip column?

The formula below is for the zip code entry.

=IF(B2="","",VLOOKUP(B2,Sheet2!$A$1:$C$40,3))

 

[Chip Pearson]

Don,

Try

=IF(B2="","",IF(ISNA(VLOOKUP(B2,Sheet2!$A$1:$C$40,3)),"",VLOOKUP(B2,Sheet2!$
A$1:$C$40,3)))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Ragdyer]

Am I missing something, but why are we not asking for an exact match by
simply adding the last argument thereby avoiding an incorrect return ?

=IF(B2="","",VLOOKUP(B2,Sheet2!$A$1:$C$40,3,0))

True, this will now display an #N/A error if the city is not present, but
it's still not a wrong answer.

To eliminate the need of a double Lookup, and also leave out the last
argument, you could try:

=IF(ISNA(MATCH(B2,Sheet2!A1:A40,0)),"",VLOOKUP(B2,Sheet2!A1:C40,3))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Don Wilkins]

,;Don,
,;
,;Try
,;
,;=IF(B2="","",IF(ISNA(VLOOKUP(B2,Sheet2!$A$1:$C$40,3)),"",VLOOKUP(B2,Sheet2!$
,;A$1:$C$40,3)))

This does not work. If I enter e.g. XYZ for a city the formula enters
the state and zip for the last entry in the lookup table. It should
leave the subsequent two cells blank because the city is not in the
table.

If the last city in the lookup table is e.g. "Walker" and I enter
"Wal" for the city then the state and zip cells are filled in with the
data for the city just above "Walker" in the lookup table. Again there
should be no entry because there is no match.

 

[Don Wilkins]

,;Am I missing something, but why are we not asking for an exact match by
,;simply adding the last argument thereby avoiding an incorrect return ?
,;
,;=IF(B2="","",VLOOKUP(B2,Sheet2!$A$1:$C$40,3,0))
,;
,;True, this will now display an #N/A error if the city is not present, but
,;it's still not a wrong answer.
,;
,;To eliminate the need of a double Lookup, and also leave out the last
,;argument, you could try:
,;
,;=IF(ISNA(MATCH(B2,Sheet2!A1:A40,0)),"",VLOOKUP(B2,Sheet2!A1:C40,3))

This does not work either. It produces the same error as the previous
suggestion.

If I enter e.g. XYZ for a city the formula enters the state and zip
for the last entry in the lookup table. It should leave the subsequent
two cells blank because the city is not in the table.

If the last city in the lookup table is e.g. "Walker" and I enter
"Wal" for the city then the state and zip cells are filled in with the
data for the city just above "Walker" in the lookup table. Again there
should be no entry because there is no match.

 

[Dave Peterson]

I think Ragdyer dropped a false when he added the final check:

=IF(ISNA(MATCH(B2,Sheet2!A1:A40,0)),"",VLOOKUP(B2,Sheet2!A1:C40,3,false))

but he did have it here (0 is equivalent to false in this situation):
=IF(B2="","",VLOOKUP(B2,Sheet2!$A$1:$C$40,3,0))

 

[Ken Schmidt]

Don: If you are still having trouble, this formula seems to work:

=IF(ISNA(VLOOKUP(B2,Sheet2!$A$1:$C$40,3,0))=TRUE,"",VLOOKUP(B2,Sheet2!$A$1:$
C$40,3,0))

In my experience, lookup formulas can be maddening. It's so easy to get the
syntax wrong. Hopefully I typed this one in correctly. Let me know if it
doesn't work!

Ken