Complex VLookup

[LadyDi]

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.

 

[NBVC]

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)

 

[LadyDi]

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

 

[LadyDi]

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

 

[LadyDi]

Will this Index formula work if the value to return is text? I have another spreadsheet that I would like to use this formula for and am having trouble getting it to work. I have a sheet with raw data that has a product ID, call date, error code, and call information. I would like this formula to look at that data, and find the call information for certain product IDs on certain dates with certain error codes. Is this possible? This is the formula that I have now:
=INDEX('Note'!U2:U5336,MATCH(1,INDEX(('Note'!F2:F5336=B22)*('Note'!E2:E5336=C20)*('Note'!K2:K5336=B21),0),0))

On the Note sheet, Column U has the call information, column F has the product IDs, column E has the call dates, and column K has the error codes. Then B22 has the Product ID I am looking for, C20 has the date I want, and B21 has the error code. When I put this formula in, instead of seeing the call information, I get a #N/A.

 

[NBVC]

Yes that formula is used to get Text strings or numbers... but only the first match (if there are more than one).

I am not sure if this relates to the other thread you recently started. If it does, continue there please so we don't confuse things.