Determine if a value falls within a range and return a corresponding value

[cvrband]

Below is an example spreadsheet showing what I'm trying to accomplish. In words, using a spilling type formula (because my spreadsheet is dynamic) I would like to determine if each value in the 'Items' list falls within a range in a table named 'Grouping'. If so, it will return the 'Group #' associated with the range that the 'Items' value fell within starting in cell C5. If the value doesn't fall within any of the ranges like AA1, 5, or BB in this example, the formula returns something that is not in the 'Group #' list like a "", -, or 0 value. Note that the grouping table may not have breaks in it as I show, so group #1 could be 1 - 5, group #2 be 6 - 9, etc.

Thank you in advance for your help writing a formula that can accomplish this task.

Book1
	A	B	C	D	E	F	G
1
2
3			Desired			Grouping
4	Items		Result		Group #	Begin	End
5	AA				1	1	4
6	AA1				2	6	9
7	1		1		3	11	12
8	2		1
9	3		1
10	4		1
11	5
12	6		2
13	7		2
14	8		2
15	9		2
16	10
17	11		3
18	12		3
19	BB
Sheet1

Cell Formulas
Range	Formula
E5:E7	E5	=SEQUENCE(COUNTA(Grouping[Begin]))
Dynamic array formulas.

 

[DanteAmor]

Try this:

Dante Amor
	A	B	C	D	E	F	G
1
2
3			Desired			Grouping
4	Items		Result		Group #	Begin	End
5	AA				1	1	4
6	AA1				2	6	9
7	1		1		3	11	12
8	2		1
9	3		1
10	4		1
11	5
12	6		2
13	7		2
14	8		2
15	9		2
16	10
17	11		3
18	12		3
19	BB
Dante Amor

Cell Formulas
Range	Formula
C7:C18	C7	=IFERROR(INDEX($E$5:$E$7,SUMPRODUCT(($F$5:$F$7<=A7)*($G$5:$G$7>=A7)*ROW($E$5:$E$7))-ROW($E$5)+1),"")

Or this:

Dante Amor
	A	B	C	D	E	F	G
1
2
3			Desired			Grouping
4	Items		Result		Group #	Begin	End
5	AA				1	1	4
6	AA1				2	6	9
7	1		1		3	11	12
8	2		1
9	3		1
10	4		1
11	5
12	6		2
13	7		2
14	8		2
15	9		2
16	10
17	11		3
18	12		3
19	BB
Dante Amor

Cell Formulas
Range	Formula
C7:C18	C7	=IFERROR(INDEX($E$5:$E$7,LOOKUP(2,1/(($F$5:$F$7<=A7)*($G$5:$G$7>=A7)),ROW($E$5:$E$7))-ROW($E$5)+1),"")

 

[cvrband]

Thank you for your reply, DanteAmor. The formulas you provide appear to work for the example given (specifically the first formula) but if I add/expand the 'Items' list with more numerical values or add more begin/end values to the 'Grouping' table, the formula ranges aren't correct anymore and will need to be manually updated. I'm finding that the F & G column references update when adding more begin/end values (I think because it is expanding the table and Excel recognizes that) but the references to ranges in the E column don't expand as more begin/end values are added. Notice in my example that the E column range expands automatically every time a new begin value is added to the table due to the formula I have in E5. Because of that, I was hoping the range could be auto-expanded with a reference to E5# but I may be way off base. I appreciate your help.

 

[DanteAmor]

You can create a table on columns e, f and g. in the formula you change the ranges by the references to the table. or try the following

=SUMIFS(E:E,F:F,"<=" & A7,G:G,">=" & A7)