0

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
tigeravatar
  • 26,199
  • 5
  • 30
  • 38
Shizzle5150
  • 13
  • 6
  • Can you provide some sample data and expected results? My guess is you're looking for something like `=INDEX(Sheet3!$A$2:$A$21,MATCH(1,INDEX((Sheet3!$B$2:$B$21<=B2)*(Sheet3!$C$2:$C$21>=B2)*(Sheet3!$D$2:$D$21<=C2)*(Sheet3!$E$2:$E$21>=C2),),0))` but I'm unable to verify without sample data and expected results. – tigeravatar Oct 17 '16 at 15:38
  • 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 – Shizzle5150 Oct 17 '16 at 15:53

1 Answers1

0

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))
tigeravatar
  • 26,199
  • 5
  • 30
  • 38
  • Awesome, this looks to be what I am trying to do... I guess that the asterisk (*) denotes AND rather than multiplication? Thanks for your time sir. – Shizzle5150 Oct 17 '16 at 16:57