Two Sheets: "Data" and "List"

"Data" has a list of Cities in Column A.
"List" has a list of Cities in Column A and corresponding zip codes in
column B.
I am trying to lookup all the appropriate zip codes and put them to column B
in the Data sheet.

So far, I am using =LOOKUP(A1,LIST!B:B,LIST!A:A). It works, but if I have
two cities that are similar (i.e. Bellwood/Bellmont), it does not give me the
exact match. How can I get an exact match? I am pulling my hair out over
this one!

Try this one: =VLOOKUP(A1,LIST!A:B,2,FALSE). This formula looks for a value
in the left column of a table and returns the value in the column # you
specify. The FALSE statement at the end of the formula is what gets you an
exact match.

"Keep It Simple Stupid" wrote:

Two Sheets: "Data" and "List"

"Data" has a list of Cities in Column A.
"List" has a list of Cities in Column A and corresponding zip codes in
column B.
I am trying to lookup all the appropriate zip codes and put them to column B
in the Data sheet.

So far, I am using =LOOKUP(A1,LIST!B:B,LIST!A:A). It works, but if I have
two cities that are similar (i.e. Bellwood/Bellmont), it does not give me the
exact match. How can I get an exact match? I am pulling my hair out over
this one!

I have tried this one before but it returns a #N/A. Both of the sheets are
formatted the same - I can't figure out why it won't work.

"Mike" wrote:

Try this one: =VLOOKUP(A1,LIST!A:B,2,FALSE). This formula looks for a value
in the left column of a table and returns the value in the column # you
specify. The FALSE statement at the end of the formula is what gets you an
exact match.

"Keep It Simple Stupid" wrote:

Two Sheets: "Data" and "List"

"Data" has a list of Cities in Column A.
"List" has a list of Cities in Column A and corresponding zip codes in
column B.
I am trying to lookup all the appropriate zip codes and put them to column B
in the Data sheet.

So far, I am using =LOOKUP(A1,LIST!B:B,LIST!A:A). It works, but if I have
two cities that are similar (i.e. Bellwood/Bellmont), it does not give me the
exact match. How can I get an exact match? I am pulling my hair out over
this one!

#N/A would mean that the vlookup formula isn't finding a matching city. Is
it possible that your dataset has trailing and/or leading spaces in the city
names? Or that some cities aren't in the List sheet at all?

"Keep It Simple Stupid" wrote:

I have tried this one before but it returns a #N/A. Both of the sheets are
formatted the same - I can't figure out why it won't work.

"Mike" wrote:

Try this one: =VLOOKUP(A1,LIST!A:B,2,FALSE). This formula looks for a value
in the left column of a table and returns the value in the column # you
specify. The FALSE statement at the end of the formula is what gets you an
exact match.

"Keep It Simple Stupid" wrote:

Two Sheets: "Data" and "List"

"Data" has a list of Cities in Column A.
"List" has a list of Cities in Column A and corresponding zip codes in
column B.
I am trying to lookup all the appropriate zip codes and put them to column B
in the Data sheet.

So far, I am using =LOOKUP(A1,LIST!B:B,LIST!A:A). It works, but if I have
two cities that are similar (i.e. Bellwood/Bellmont), it does not give me the
exact match. How can I get an exact match? I am pulling my hair out over
this one!

I thought about that before, so I did a LEN formunla and it didn't look like
I had any leading/trailing spaces. After you mentioned it again, I did a
TRIM formula and now it all works.

Thanks for your help!

"Mike" wrote:

#N/A would mean that the vlookup formula isn't finding a matching city. Is
it possible that your dataset has trailing and/or leading spaces in the city
names? Or that some cities aren't in the List sheet at all?

"Keep It Simple Stupid" wrote:

I have tried this one before but it returns a #N/A. Both of the sheets are
formatted the same - I can't figure out why it won't work.

"Mike" wrote:

Try this one: =VLOOKUP(A1,LIST!A:B,2,FALSE). This formula looks for a value
in the left column of a table and returns the value in the column # you
specify. The FALSE statement at the end of the formula is what gets you an
exact match.

"Keep It Simple Stupid" wrote:

Two Sheets: "Data" and "List"

"Data" has a list of Cities in Column A.
"List" has a list of Cities in Column A and corresponding zip codes in
column B.
I am trying to lookup all the appropriate zip codes and put them to column B
in the Data sheet.

So far, I am using =LOOKUP(A1,LIST!B:B,LIST!A:A). It works, but if I have
two cities that are similar (i.e. Bellwood/Bellmont), it does not give me the
exact match. How can I get an exact match? I am pulling my hair out over
this one!

You're welcome. Glad I could help!

"Keep It Simple Stupid" wrote:

I thought about that before, so I did a LEN formunla and it didn't look like
I had any leading/trailing spaces. After you mentioned it again, I did a
TRIM formula and now it all works.

Thanks for your help!

"Mike" wrote:

#N/A would mean that the vlookup formula isn't finding a matching city. Is
it possible that your dataset has trailing and/or leading spaces in the city
names? Or that some cities aren't in the List sheet at all?

"Keep It Simple Stupid" wrote:

I have tried this one before but it returns a #N/A. Both of the sheets are
formatted the same - I can't figure out why it won't work.

"Mike" wrote:

Try this one: =VLOOKUP(A1,LIST!A:B,2,FALSE). This formula looks for a value
in the left column of a table and returns the value in the column # you
specify. The FALSE statement at the end of the formula is what gets you an
exact match.

"Keep It Simple Stupid" wrote:

Two Sheets: "Data" and "List"

"Data" has a list of Cities in Column A.
"List" has a list of Cities in Column A and corresponding zip codes in
column B.
I am trying to lookup all the appropriate zip codes and put them to column B
in the Data sheet.

So far, I am using =LOOKUP(A1,LIST!B:B,LIST!A:A). It works, but if I have
two cities that are similar (i.e. Bellwood/Bellmont), it does not give me the
exact match. How can I get an exact match? I am pulling my hair out over
this one!

Your table in A has to be sorted ascending; Bellmont before Bellwood

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Keep It Simple Stupid" wrote in message
...
| Two Sheets: "Data" and "List"
|
| "Data" has a list of Cities in Column A.
| "List" has a list of Cities in Column A and corresponding zip codes in
| column B.
| I am trying to lookup all the appropriate zip codes and put them to column B
| in the Data sheet.
|
| So far, I am using =LOOKUP(A1,LIST!B:B,LIST!A:A). It works, but if I have
| two cities that are similar (i.e. Bellwood/Bellmont), it does not give me the
| exact match. How can I get an exact match? I am pulling my hair out over
| this one!