Look here:
http://office.microsoft.com/en-us/assistance/HA012260381033.aspx
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
|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!
