finding a single value in one tab, within a range in another tab

[Andy Wiles]

Hi,

I'm way stumped on this one so wondering if anyone can help out.

Not sure if i can attach a file anywhere so i'll explain.

tab 1,,
a list of phone numbers in column A so something like

02077481000
02077481001
02077481003 etc etc, list is hundreds of thousands of lines long.

tab 2,,,
A list of ranges

02077481000 - 02077481025
02077481030 - 02077489999 etc etc, again a massive list of ranges.

i want to put a formula next to each of the individual numbers in Tab 1, which basically looks up if the single number is listed within tab 2.

Prob i have is that a vlookup will recognize that if i'm looking for 02077481000 then its there, But if i do the same lookup on 02077481001
is had no concept of this number falling within the range of 02077481000 - 02077481025.

hopefully that makes sense ?

many thanks in advance

Andy

 

[AlanY]

this works with helper columns

Excel 2013/2016
	A	B	C	D	E	F
1	02077481001	match		02077481000 - 02077481025	2077481000	2077481025
2	02077481105	match		02077481026 - 02077481050	2077481026	2077481050
3	02077501101			02077481051 - 02077481075	2077481051	2077481075
4				02077481076 - 02077481100	2077481076	2077481100
5				02077481101 - 02077481125	2077481101	2077481125
6				02077481126 - 02077481150	2077481126	2077481150
7				02077481151 - 02077481175	2077481151	2077481175
8				02077481176 - 02077481200	2077481176	2077481200
Sheet2

Cell Formulas
Range	Formula
E1	=TRIM(LEFT(SUBSTITUTE(D1,"-",REPT(" ",500)),500))*1
F1	=TRIM(RIGHT(SUBSTITUTE(D1,"-",REPT(" ",500)),500))*1
B1	=IF(SUMPRODUCT(((A1*1)>=$E$1:$E$8)*((A1*1)<=$F$1:$F$8))=1,"match","")

 

[Momentman]

Here's one way

Book1
	A	B	C	D	E	F
1	02077481000	21			02077481000 - 02077481025	21
2	02077481001	21			02077481030 - 02077489999	12
3	02078483456	23			02077491030 - 02077499999	24
4	02077491133	24			02078481030 - 02078489999	23
Beta (9)

Cell Formulas
Range	Formula
B1	=SUMPRODUCT((A1>=LEFT($E$1:$E$4,SEARCH(" - ",$E$1:$E$4)-1))*(A1<=RIGHT($E$1:$E$4,LEN($E$1:$E$4)-SEARCH(" - ",$E$1:$E$4)-2))*($F$1:$F$4))

Am using same sheet here, but the idea is the same