Formula to determine what range an item/value falls within

[cvrband]

Greetings - Please see the 3 examples below. I'm looking for a formula (if possible) to determine what range an item falls within and return the range number in the "Range Result" column. In my actual spreadsheet, the Item List is a named range (for this example call it 'ItemList'), the range numbers always start at 1 and are sequential (could be 20 or more ranges in total), and the min/max values and the total number of ranges are based on user input. Range 1's minimum value should be the first item in the list and the last range's max value should be the last item in the list. Thanks in advance!

Value in Range.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2	EXAMPLE A								EXAMPLE B								EXAMPLE C
3	Item	Range							Item	Range							Item	Range
4	List	Result		Range	Min	Max			List	Result		Range	Min	Max			List	Result		Range	Min	Max
5	LWT1	1		1	LWT1	2			DWT	1		1	DWT	2			1	1		1	1	2
6	LWT2	1		2	3	7			1	1		2	3	6			2	1		2	3	6
7	1	1		3	8	9			2	1		3	7	9			3	2		3	7	9
8	2	1		4	10	RWT			3	2		4	10	13			4	2		4	10	12
9	3	2							4	2		5	14	15			5	2		5	13	WWT3
10	4	2							5	2		6	16	18			6	2
11	5	2							6	2							7	3
12	6	2							7	3							8	3
13	7	2							8	3							9	3
14	8	3							9	3							10	4
15	9	3							10	4							11	4
16	10	4							11	4							12	4
17	11	4							12	4							13	5
18	12	4							13	4							WWT2	5
19	13	4							14	5							WWT3	5
20	RWT	4							15	5
21									16	6
22									17	6
23									18	6
24
Sheet1

 

[FormR]

Hi, something like this maybe?

Book1
	A	B	C	D	E	F
1
2	EXAMPLE A
3	Item	Range
4	List	Result		Range	Min	Max
5	LWT1	1		1	LWT1	2
6	LWT2	1		2	3	7
7	1	1		3	8	9
8	2	1		4	10	RWT
9	3	2
10	4	2
11	5	2
12	6	2
13	7	2
14	8	3
15	9	3
16	10	4
17	11	4
18	12	4
19	13	4
20	RWT	4
Sheet1

Cell Formulas
Range	Formula
E5:E8	E5	=XLOOKUP($D5,$B$5:$B$20,$A$5:$A$20,"",0,1)
F5:F8	F5	=XLOOKUP($D5,$B$5:$B$20,$A$5:$A$20,"",0,-1)

 

[shift-del]

An enhancement to FormR's solution.

D5

=UNIQUE(B5:B20)

E5

=XLOOKUP(D5#,B5:B20,A5:A20,,0,{1.-1})

 

[cvrband]

Hello FormR - Thanks for your reply. Maybe I wasn't clear but I'm looking for the formula to return the results as shown in columns B, J, and R. The formula would be located in say B5# and look up the item in column A to determine which range the Item falls within in columns E & F returning that range's value from Column D. My columns B, J, and R are examples of what the formula should return.

 

[shift-del]

Then try this.

=XLOOKUP(ROWS(B$5:B5),XMATCH($E$5:$E$8,$A$5:$A$20,0),$D$5:$D$8,,-1,1)

 

[cvrband]

shift-del - that solution works! Thank you!!