Best method of index match or vlookup or other method for street data

[xdriver]

I have school district data that states what street address & street numbers are associated with what schools a child will attend. I also have a list of street address. I would like reference the district data and create two columns next to the street address that state what school a child would attend if they lived there.

I have added the helper column as an "idea" if an if then statement was included in the formula (as this is the only way I can think of). My next thought was to list all the addresses that have the additional note data as an additional row. For instance I would manually add another 326 rows with data such as 1 Bacon St, 2 Bacon St, etc. and match the schools manually as well. Out of 700 street names, 50 of them have the additional data that splits schools for specific streets such as the Bacon St and Bishop St reference.

I don't know where to go from here or the best method. I posted this another way a few days ago but wasn't able to get the tables to insert. The plugin has since been updated by the developer and is the reason I am posting again with the additional data. Thank you


Excel 2016 (Mac) 32 bit


[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]Street[/TD]
[TD]Helper[/TD]
[TD]Notes[/TD]
[TD]Elementary School[/TD]
[TD]Middle School[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]Abbott Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]Alden Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]Alger Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD]Algonquian Drive[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD]Allen Court[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD]Allen Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Johnson[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8​

[/TD]
[TD]Allison Way[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9​

[/TD]
[TD]Ambler Court[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Johnson[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

10​

[/TD]
[TD]Andrew Circle[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

11​

[/TD]
[TD]Apple Ridge Drive[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

12​

[/TD]
[TD]Apple Tree Lane[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

13​

[/TD]
[TD]Appleton Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

14​

[/TD]
[TD]Aqueduct Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

15​

[/TD]
[TD]Arbor Circle[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

16​

[/TD]
[TD]Arcadia Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

17​

[/TD]
[TD]Archer Drive[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Brown[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

18​

[/TD]
[TD]Arlington Circle[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

19​

[/TD]
[TD]Arlington Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

20​

[/TD]
[TD]Arrow Path[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

21​

[/TD]
[TD]Arthur Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

22​

[/TD]
[TD]Ash Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

23​

[/TD]
[TD]Atherton Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Johnson[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

24​

[/TD]
[TD]Auburn Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

25​

[/TD]
[TD]Austin Way[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

26​

[/TD]
[TD]Autumn Lane[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

27​

[/TD]
[TD]Avon Lane[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Brown[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

28​

[/TD]
[TD]Avon Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

29​

[/TD]
[TD]Azalea Circle[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

30​

[/TD]
[TD]Bacon Street[/TD]
[TD]

1​

[/TD]
[TD]1-186[/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

31​

[/TD]
[TD]Bacon Street[/TD]
[TD]

1​

[/TD]
[TD]187 and up[/TD]
[TD]Ben Hem[/TD]
[TD]Wilson[/TD]
[TD]Bacon St 326 max[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

32​

[/TD]
[TD]Badger Avenue[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

33​

[/TD]
[TD]Bailey Hill Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

34​

[/TD]
[TD]Barchstead Place[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

35​

[/TD]
[TD]Barnesdale Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Brown[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

36​

[/TD]
[TD]Bass Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

37​

[/TD]
[TD]Bass Terrace[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

38​

[/TD]
[TD]Bay State Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

39​

[/TD]
[TD]Bayberry Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

40​

[/TD]
[TD]Beacon Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

41​

[/TD]
[TD]Beaconsfield Drive[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

42​

[/TD]
[TD]Bear Hill Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Johnson[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

43​

[/TD]
[TD]Beaver Dam Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Brown[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

44​

[/TD]
[TD]Bee Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Johnson[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

45​

[/TD]
[TD]Bellevue Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

46​

[/TD]
[TD]Belmore Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Brown[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

47​

[/TD]
[TD]Belvedere Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

48​

[/TD]
[TD]Bennett Street[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Johnson[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

49​

[/TD]
[TD]Beverly Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

50​

[/TD]
[TD]Bigelow Avenue[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Lilja[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

51​

[/TD]
[TD]Birch Road[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

52​

[/TD]
[TD]Bishop Street[/TD]
[TD]

1​

[/TD]
[TD]6/16/18/20/21/22/24/ 28/377[/TD]
[TD]Brown[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: School district[/TD]
[/TR]
</tbody>[/TABLE]


Excel 2016 (Mac) 32 bit


[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

405​

[/TD]
[TD]2 Avon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

406​

[/TD]
[TD]3 Avon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

407​

[/TD]
[TD]4 Avon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

408​

[/TD]
[TD]6 Avon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

409​

[/TD]
[TD]7 Avon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

410​

[/TD]
[TD]8 Avon St - Unit 8[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

411​

[/TD]
[TD]9 Avon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

412​

[/TD]
[TD]1 Azalea Cir - Unit 1[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

413​

[/TD]
[TD]3 Azalea Cir[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

414​

[/TD]
[TD]4 Azalea Cir[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

415​

[/TD]
[TD]5 Azalea Cir[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

416​

[/TD]
[TD]0 Bacon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

417​

[/TD]
[TD]101 Bacon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

418​

[/TD]
[TD]102 Bacon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

419​

[/TD]
[TD]103 Bacon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

420​

[/TD]
[TD]104 Bacon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

421​

[/TD]
[TD]105 Bacon St[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

422​

[/TD]
[TD]106 Bacon St[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Street Addresses[/TD]
[/TR]
</tbody>[/TABLE]

 

[CalcSux78]

You could use a combination of Index Match with multiple criteria if we had a lower and upper bound for the street index. The only issue I'm unsure of is how to handle Bishop Street in your example.

Here's what I have so far.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Street	Lbound	Ubound	Elementary School	Middle School	Only These		Number	Street	Elementary	Middle
2	Abbott Road	1	99999	Ben-Hem	Wilson			269	Bacon Street	Ben Hem	Wilson
3	Alden Street	1	99999	Ben-Hem	Wilson
4	Alger Street	1	99999	Memorial	Kennedy
5	Algonquian Drive	1	99999	Lilja	Wilson
6	Allen Court	1	99999	Lilja	Wilson
7	Allen Street	1	99999	Johnson	Wilson
8	Allison Way	1	99999	Memorial	Kennedy
9	Ambler Court	1	99999	Johnson	Wilson
10	Andrew Circle	1	99999	Ben-Hem	Wilson
11	Apple Ridge Drive	1	99999	Memorial	Kennedy
12	Apple Tree Lane	1	99999	Ben-Hem	Wilson
13	Appleton Road	1	99999	Ben-Hem	Wilson
14	Aqueduct Road	1	99999	Memorial	Kennedy
15	Arbor Circle	1	99999	Lilja	Wilson
16	Arcadia Road	1	99999	Ben-Hem	Wilson
17	Archer Drive	1	99999	Brown	Kennedy
18	Arlington Circle	1	99999	Ben-Hem	Wilson
19	Arlington Road	1	99999	Ben-Hem	Wilson
20	Arrow Path	1	99999	Memorial	Kennedy
21	Arthur Street	1	99999	Ben Hem	Wilson
22	Ash Street	1	99999	Ben-Hem	Wilson
23	Atherton Street	1	99999	Johnson	Wilson
24	Auburn Street	1	99999	Memorial	Kennedy
25	Austin Way	1	99999	Lilja	Wilson
26	Autumn Lane	1	99999	Memorial	Kennedy
27	Avon Lane	1	99999	Brown	Kennedy
28	Avon Street	1	99999	Lilja	Wilson
29	Azalea Circle	1	99999	Lilja	Wilson
30	Bacon Street	1	186	Lilja	Wilson
31	Bacon Street	187	326	Ben Hem	Wilson
32	Badger Avenue	1	99999	Memorial	Kennedy
33	Bailey Hill Road	1	99999	Memorial	Kennedy
34	Barchstead Place	1	99999	Memorial	Kennedy
35	Barnesdale Road	1	99999	Brown	Kennedy
Schools

Cell Formulas
Range	Formula
J2	{=IFERROR(INDEX(D:D,MATCH(1,(I2=A:A)*(H2>=B:B)*(H2<=C:C),0),0),"*No Match*")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[xdriver]

Thank you for this. For some reason this does not seem to be working for me. I assume it has to do with the max min inside the equation. I get "*No Match*" for all the J column. Thanks.

Unknown 32 bit


[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[TH]

I​

[/TH]
[TH]

J​

[/TH]
[TH]

K​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]Street[/TD]
[TD]Lbound[/TD]
[TD]Ubound[/TD]
[TD]Elementary School[/TD]
[TD]Middle School[/TD]
[TD][/TD]
[TD]Notes[/TD]
[TD]Number[/TD]
[TD]Street[/TD]
[TD]Elementary[/TD]
[TD]Middle[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]Abbott Road[/TD]
[TD]

1​

[/TD]
[TD]

99999​

[/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]Abbott Road[/TD]
[TD]=IFERROR(INDEX(D:D,MATCH(1,(I2=A:A)*(H2>=B:B)*(H2<=C:C),0),0),"*No Match*")[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]Alden Street[/TD]
[TD]

1​

[/TD]
[TD]

99999​

[/TD]
[TD]Ben-Hem[/TD]
[TD]Wilson[/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]Avon Street[/TD]
[TD]=IFERROR(INDEX(D:D,MATCH(1,(I3=A:A)*(H3>=B:B)*(H3<=C:C),0),0),"*No Match*")[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]Alger Street[/TD]
[TD]

1​

[/TD]
[TD]

99999​

[/TD]
[TD]Memorial[/TD]
[TD]Kennedy[/TD]
[TD][/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]Avon Street[/TD]
[TD]=IFERROR(INDEX(D:D,MATCH(1,(I4=A:A)*(H4>=B:B)*(H4<=C:C),0),0),"*No Match*")[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Schools working[/TD]
[/TR]
</tbody>[/TABLE]

 

[xdriver]

Nevermind, I noticed that the { } didn't show in the pasted formula and was able to repair it, it now works as intended.

Is there any way to reference a cell that has a formula in it. For instance use the same format, except replace the >= statements in the formula above, with a cell that includes >= ? That way I could also possibly list out odds or evens in a cell?