How to determine which range a value falls within and output a value from the same row

Try this : F2 --> =SUMPRODUCT(($A$2:$A$5<=E2)*($B$2:$B$5>=E2)*($C$2:$C$5))

No, that formula will not return text values.

The values and min/max ranges will not necessarily be sorted in order

Try this...


Data Range

	A	B	C	D	E	F
1	min	max	Regon	-----	collection #	Region
2	33	56	North		75	East
3	73	92	East		35	North
4	108	121	South		109	South
5	137	168	West		120	South
6					42	North
7					138	West
8					152	West

This array formula** entered in F2 and copied down:

=IFERROR(INDEX(C$2:C$5,MATCH(1,IF(E2>=A$2:A$5,IF(E2<=B$2:B$5,1)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Good evening.

If you donīt know when you will have text or value as a result of SUMPRODUCT function, try this one:

Before:
F2 --> =SUMPRODUCT(($A$2:$A$5<=E2)*($B$2:$B$5>=E2)*($C$2:$C$5))

Now:
F2 --> =INDIRECT("C"&SUMPRODUCT(($A$2:$A$5<=F2)*($B$2:$B$5>=F2)*ROW($C$2:$C$5)),1)

Please, tell us if it worked for you.


Greetings from Brazil