vlookup? finding multiple zipcodes for a single city

Question

I have a spreadsheet with 2 columns: A) Cities B) Zip codes.  I'm trying to create a vlookup in which when I type in a city name it brings back the corresponding zip codes.  The problem is, some cities have multiple zipcodes. What forumla can I use so it will bring back all possible zipcodes for that city? Thanks!

Answer 1

Hi,

Try this ARRAY formula; see below on how to enter it. Put it in a cell and ARRAY enter it and drag down (say) 20 rows. Enter a city name in E1 and it will return all the zip codes for that city. It will return blanks until you enter a city code.

=IFERROR(INDEX($B$1:$B$2000,SMALL(IF($A$1:$A$2000=$E$1,ROW($A$1:$A$2000)-ROW($A$1)+1),ROWS(B$1:B1))),"")

This is an array formula which must be entered by pressing CTRL+Shift+Enter

and not just Enter. If you do it correctly then Excel will put curly brackets

around the formula {}. You can't type these yourself. If you edit the formula

you must enter it again with CTRL+Shift+Enter.