Index/Match-corresponding zip code in column

I'm having some issues writing a formula that will yield the zip code associated with the city and state as an exact match. For example I need an exact match for city and state in cell C2 (sheet1) to produce the corresponding zip code in column B (Sheet2). Any help would be appreciated.

Hi Mikera,

To avoid using array formulas, I'd suggest adding the following formula into cell H2 on Sheet2 and filling down to your last row of data. It will create a concatenated list of cities and states that you can use to lookup.

=A2&E2

Then on Sheet1 in cell C2 you can use the following formula to find the matching zip code:

=INDEX(Sheet2!B:B,MATCH(A2&B2,Sheet2!H:H,0))

Fill that down column C as far as necessary to get the rest of your zip codes. If you can't create a helper column (column H) on Sheet2, you could try this array formula in Sheet1 cell C2:

=INDEX(Sheet2!B1:B100,MATCH(A2&B2,Sheet2!A1:A100&Sheet2!E1:E100,0))

This formula must be confirmed with CTRL+SHIFT+ENTER, not just ENTER. Adjust your ranges to match your actual ranges (B1:B400, etc.)