EXCEL index match by multiple criteria AND multiple match types?

Question

So I am sure I just have the syntax wrong but here is the situation....

I have Sheet2 with 3 columns: point_name, x, y I have Sheet3 with a list of areas with 5 columns: area_name, x_start, x_end, y_start, y_end

I am trying to associate each X and Y coord with it's respective area. I know a index match will allow for multiple criteria but it seems it will not allow for multiple match types....

=INDEX(Sheet3!A2:A64,MATCH(Sheet2!B2&Sheet2!C2&Sheet2!B2&Sheet2!C2,Sheet3!$C$2:$C$65&Sheet3!$E$2:$E$65&Sheet3!$B$2:$B$65&Sheet3!$D$2:$D$65,-1&-1&1&1))

The problem I am running into (or I think the problem I am running into) is that while match will allow for multiple criteria, it does not allow for multiple match types IE -1&1 greater than&less than.

Perhaps I am just going about this the wrong way so as a brief recap

I have a list of areas and the respective boundaries for each in sheet3

I want to

return the Sheet3 area_name where

sheet2 x is greater than sheet3 x_end And

sheet2 x less than sheet3 x_start And

sheet2 y greater than sheet3 y_end And

sheet2 y less than sheet3 y_start

in actuality <= >= to would be better but I don't want to complicate it until a get the result I am looking for.

Am I barking up the wrong tree with an index match function? Is there a better way to do it that I am missing?

I am not opposed to using a macro with a foreach row in sheet2 if that would be a better way to do it.

Thanks for your time.

EDIT

Sample Data:

Sheet2
point_name  __x_  __y_
point1      1060  6090
point2      1200  6080
point3      1232  5750

Sheet3
area_name  y_start   y_end  x_start  x_end
Area1         6106    6080     1149   1055
Area2         6106    6080     1315   1163
Area3       6227.5  6115.5     1115   1095
Area4         5860    5730     1239   1229

Answer 1

Using your provided sample data, the results would be "Area1", "Area2", and "Area4". Those results are achieved using this formula:

=INDEX(Sheet3!$A$2:$A$5,MATCH(1,INDEX((Sheet3!$D$2:$D$5>=B2)*(Sheet3!$E$2:$E$5<=B2)*(Sheet3!$B$2:$B$5>=C2)*(Sheet3!$C$2:$C$5<=C2),),0))