Index Match Multiple Results from Multiple Criteria

[Azira]

Good Day all,
It's been quite a while since I've had to post a question here. I've searched many, many posts about this and have no luck finding the solution.
Note: I only have Excel 2016, so 365 solutions won't do me any good.

My goal is to input the criteria (yellow) and return a list of names (Green) that match the criteria.
Yellow cells are the inputted criteria. Green are what's expected. (the highlights are only for clarification)
The From and To columns are always low to high, if that helps.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Area	Segment	Region	From	To	Name					number between
2	North	100	Plains	0	100	Jim		Area	Segment	Region	from and to
3	South	5150	Mountains	100	523	Steve		South	5150	Mountains	125
4	East	1	Swamps	0	35	Alice
5	West	500	Boreal	0	9999	James
6	South	5150	Mountains	10	50	Bill					Names
7	East	685	Plains	25	150.2	Bob					Steve
8	East	7000	Mountains	0	10.5	Harry					Jane
9	South	5150	Mountains	35	238	Jane
10	West	63	Boreal	0	25	Nancy
11	North	685	Boreal	25	55	Chris
12
Sheet1

Thanks for any help

 

[Fluff]

I only have Excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=IFERROR(INDEX($F$2:$F$11,AGGREGATE(15,6,(ROW($F$2:$F$11)-ROW($F$2)+1)/($A$2:$A$11=$H$3)/($B$2:$B$11=$I$3)/($C$2:$C$11=$J$3)/($D$2:$D$11<=$K$3)/($E$2:$E$11>=$K$3),ROWS(K$7:K7))),"")

 

[Azira]

Wow!
I think I was on the right track with using Aggregate, but I just couldn't figure it out.
Thank you so much. I'm going to enjoy picking it apart to see how it works.
Something I noticed though, on other typical index matches, the criteria are separated with an asterisk (criteria1=this)*(criteria2=that) etc...
Are the slashes a way to separate the criteria or is there actual division going on?

And I updated my profile btw.

 

[Fluff]

I updated my profile btw.

Thanks for that

When using AND criteria inside Aggregate, you can either use the multiplication or the division, it's just a matter of personal preference.