Confirm a value from a range

[thebigbadbull55]

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.

Zip Start	Zip End	Transit Days		Possible Zips
04237	04241	3		04237
04252	04258	3		04238
				04239
				04240
				04241
				04242
				04243
				04244
				04245
				04246
				04247
				04248
				04249
				04250
				04251
				04252
				04253
				04254

<tbody>
</tbody>

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.

 

[Roderick_E]

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:

=(MATCH(B3,E:E,0)-1)-MATCH(A3,E:E,0)

 

[thebigbadbull55]

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.

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:

=(MATCH(B3,E:E,0)-1)-MATCH(A3,E:E,0)

 

[Fluff]

Here's a UDF is your interested.

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
	A	B	C	D	E	F
1	Zip Start	Zip End	Transit Days		Possible Zips
2	4237	4241	4		4237	4
3	4252	4258	3		4238	4
4					4239	4
5					4240	4
6					4241	4
7					4242
8					4243
9					4244
10					4245
11					4246
12					4247
13					4248
14					4249
15					4250
16					4251
17					4252	3
18					4253	3
19					4254	3
Sheet2

Cell Formulas
Range	Formula
F2	=ZipTransit(E2,A$2:C$3)

 

[Roderick_E]

Here's a UDF is your interested.

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

	A	B	C	D	E	F
1	Zip Start	Zip End	Transit Days		Possible Zips
2	4237	4241	4		4237	4
3	4252	4258	3		4238	4
4					4239	4
5					4240	4
6					4241	4
7					4242
8					4243
9					4244
10					4245
11					4246
12					4247
13					4248
14					4249
15					4250
16					4251
17					4252	3
18					4253	3
19					4254	3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula F2 =ZipTransit([COLOR=rgb(255]E2,A$2:C$3[/COLOR]) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.

 

[Fluff]

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.

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

 

[thebigbadbull55]

I am not familiar with UDF's. Any chance you have steps to implement this?

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

 

[thebigbadbull55]

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!!

 

[Fluff]

Glad we could help & thanks for the feedback