Confirm a value from a range

T

thebigbadbull55

New Member
Joined
Sep 11, 2018
Messages
7
Good Afternoon!

I am trying to figure out a way/formula option to take a range data set, confirm if a value is in that range, then display a value.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Zip Start[/TD]
[TD]Zip End[/TD]
[TD]Transit Days[/TD]
[TD][/TD]
[TD]Possible Zips[/TD]
[/TR]
[TR]
[TD]04237[/TD]
[TD]04241[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]04237[/TD]
[/TR]
[TR]
[TD]04252[/TD]
[TD]04258[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]04238[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04239[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04240[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04241[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04242[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04243[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04244[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04245[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04246[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04247[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04248[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04249[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04250[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04251[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04252[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04253[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04254[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So with the data above, I am trying to determine in the values from column: Possible Zip are equal to or between the Zip Start and Zip End values, and if so, display the Transit Days value. I have about 8000 rows, so there is a lot of data to confirm and would need a formula that would be able to work for a large range.

I have had no luck with lookups, countifs, etc.


Any help on this would be extremely appreciated.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Well, you'll obviously want to make sure no one ever sorts your "possible" list. But anyhow, you would use =(match(destzip,possiblerange,0)-1)-match(orgzip,possiblerange,0)

If your columns started at column A it would look like this:
Code: 
=(MATCH(B3,E:E,0)-1)-MATCH(A3,E:E,0)
 
Upvote 0
Thank you for the response!

This is on the right track, however it isn't exactly what I am looking for.

It might be that we have to add another function to this formula, so that we can confirm that the values in the possible zip range are actually between the values in the start and end zip ranges. Not sure how we can take the below formula down the possible zip list to confirm yes/no if the value exists in the range.



Roderick_E said:
Well, you'll obviously want to make sure no one ever sorts your "possible" list. But anyhow, you would use =(match(destzip,possiblerange,0)-1)-match(orgzip,possiblerange,0)

If your columns started at column A it would look like this:
Code: 
=(MATCH(B3,E:E,0)-1)-MATCH(A3,E:E,0)
Click to expand...
 
Upvote 0
Here's a UDF is your interested.
Code: 
Function ZipTransit(cl As Range, rng As Range)
   Dim i As Long
   ZipTransit = ""
   For i = 1 To rng.Rows.count
      If cl >= rng(i, 1) And cl <= rng(i, 2) Then
         ZipTransit = rng(i, 3)
         Exit For
      End If
   Next i
End Function


Excel 2013 32 bit
ABCDEF
1Zip StartZip EndTransit DaysPossible Zips
242374241442374
342524258342384
442394
542404
642414
74242
84243
94244
104245
114246
124247
134248
144249
154250
164251
1742523
1842533
1942543
Sheet2
Cell Formulas
RangeFormula
F2=ZipTransit(E2,A$2:C$3)
 
Upvote 0
Fluff said:
Here's a UDF is your interested.
Code: 
Function ZipTransit(cl As Range, rng As Range)
   Dim i As Long
   ZipTransit = ""
   For i = 1 To rng.Rows.count
      If cl >= rng(i, 1) And cl <= rng(i, 2) Then
         ZipTransit = rng(i, 3)
         Exit For
      End If
   Next i
End Function

Excel 2013 32 bit
ABCDEF
Zip StartZip EndTransit DaysPossible Zips

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]4237[/TD]
[TD="align: right"]4241[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4237[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4252[/TD]
[TD="align: right"]4258[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4238[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4239[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4240[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4241[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4242[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4243[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4244[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4245[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4246[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4247[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4248[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4249[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4250[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4251[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4252[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4253[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4254[/TD]
[TD="align: right"]3[/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]F2[/TH]
[TD="align: left"]=ZipTransit([COLOR=rgb(255]E2,A$2:C$3[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

After posting my solution, I was thinking that it doesn't work if the org zip code is further in the list than the dest zip. @Fluff does your UDF account for that.
Also sounds like bigbadbull wants a way to return "NO" if either org or dest zip don't match anything in the possible list.
 
Upvote 0
After posting my solution, I was thinking that it doesn't work if the org zip code is further in the list than the dest zip. @Fluff does your UDF account for that.
Click to expand...
Not sure I understand.
The udf will check any zip code you feed into it, check if that occurs in any of the Start/end ranges & if so returns the transit time
 
Upvote 0
I was actually able to find a user guide online to get excel formatted to take the custom function!

Got it working!

this is perfect!!!

thank you very much for the help!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.
Replies
6
Views
2K
nemmi69
nemmi69
S
  • Question Question
Copies range from the wrong sheet
Replies
5
Views
583
kevin9999
K
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
562
Greenbehindthecells
G
Z
  • Question Question
Sum Values by month from 1st day to last day of each month, and specific date range
Replies
6
Views
470
alansidman
A
E
  • Solved
Is there a way to ignore year in calculating duration of days?
Replies
3
Views
125
Joe4
Joe4

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top