I have a spreadsheet displaying beginning zip codes, ending zip codes, county, city, state, and tax code. I need to create a look up that will find a zip code in the range between beginning zip code and ending zip code, then match a city within that range, and return the correct tax code. My problem occurs, because I can have a zip code that falls between the beginning and ending zip code (so I won't have an exact match).

On my spreadsheet, I could have a records like this:

Beginning Ending State County City Tax Code
44709 44721 Ohio Stark North Canton 1234
44709 44721 Ohio Stark Canton 1235
44709 44721 Ohio Stark Massillon 1236

Then say I need to find the tax code for zip code 44718 in North Canton. How can I write a formula that will look all the entries where 44718 can fall and then find North Canton and return the tax code for that (resulting in 1234). I can get this to work, if the zip code can only be in one row, but I can't get it to work in the example above. Any assistance you can provide would be greatly appreciated.

Assuming database is in A1:F4 where Start/End Zips are in A1:A4 and B1:B4 and the cities are in E2:E4, and your search Zip input is in I2 and your City is in in J2, then try:

=INDEX($F$2:$F$4,MATCH(1,INDEX(($A$2:$A$4<=I2)*($B$2:$B$4>=I2)*($E$2:$E$4=J2),0),0))

if the Tax Code is always numeric, this should also work:

=SUMPRODUCT(($F$2:$F$4,($A$2:$A$4<=I2)*($B$2:$B$4>=I2)*($E$2:$E$4=J2))

and if you have Excel 2007 or later:

=SUMIFS($F$2:$F$4,$A$2:$A$4,"<="&I2,$B$2:$B$4,">="&I2,$E$2:$E$4,J2)

I tried all three of the formulas you provided, and I either got #N/A or 0 as the result.

I got it to work. The city and zip were in the wrong cells. Thank you so much for your help.