Vlookup or similar formula on range between columns

ks49506

New Member
Joined
Dec 7, 2017
Messages
3
Greetings -
I've seen a few questions get close but return what I am looking for but not exact.
I have a sheet with ranges of zip codes, columns A and B, and a transit time in C. So if a zip code is found in A, B or between then the transit time is C. A zip code not found in A, B or between it is not serviced.

Start ZipEnd ZipService Days
14020140211
14024140281
14029140292
14030140381

<tbody>
</tbody>

I have a lookup sheet to get the transit time for the zip code plugged in by a user.
Destination ZIPTransit
49506(Return)

<tbody>
</tbody>

If the zip codes is not serviced (it is not in column A, B or between) then the return should be Blank or "Not Serviced" (whatever is fine). I'll probably write an IfError formula around it.

I've tried =IFERROR(VLOOKUP(B6,Sheet1!A:C,3,TRUE),"") but is returning results for zip codes that are not covered in A, B or between.
For example, if I type in 14023, it returns "1" when 14023 is not listed or in a range.

I have non-tech savvy folks using this sheet - I would rather not use an array.

Thanks for the help!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Code:
=IF(SUMPRODUCT(--(B6>=Sheet1!A:A), --(B6<=Sheet1!B:B), Sheet1!C:C)=0, "Not Serviced", SUMPRODUCT(--(B6>=Sheet1!A:A), --(B6<=Sheet1!B:B), Sheet1!C:C))
 
Last edited:

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
Double dash makes the Boolean value in the parenthesis numeric. For example, if the value of "B6>=Sheet1!A:A" is True then it converts to 1. And it converts False to 0.
If you are getting error, please make sure the ranges and the sheet names are correct and you don't forget any parenthesis.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,376
Messages
5,595,814
Members
414,023
Latest member
HashiramaSenju

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top