Lookup Exact Match
 

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!

Lookup Exact Match
 

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!

Lookup Exact Match
 

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!

Lookup Exact Match
 

#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!

Lookup Exact Match
 

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!

Lookup Exact Match
 

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!

Lookup Exact Match
 

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!

Lookup Exact Match
 

I have one other question. As you know, some cities have more than one zip
code. Is there a way to take this same formula
[=VLOOKUP(A1,LIST!A:B,2,FALSE)] and report all the options? For example,
when matching Appleton, it will show 54911 and 54912. (I don't care how many
extra columns it takes if they need to report in separate columns.)
(Sorry, I should have had some foresight on this one!)

"Mike" wrote:

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!

Lookup Exact Match
 

Look he

http://office.microsoft.com/en-us/as...260381033.aspx


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Keep It Simple Stupid" wrote in message
...
|I have one other question. As you know, some cities have more than one zip
| code. Is there a way to take this same formula
| [=VLOOKUP(A1,LIST!A:B,2,FALSE)] and report all the options? For example,
| when matching Appleton, it will show 54911 and 54912. (I don't care how many
| extra columns it takes if they need to report in separate columns.)
| (Sorry, I should have had some foresight on this one!)
|
| "Mike" wrote:
|
| 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!