Range lookup for x and y - sumproduct? index?

Hi, I've been working on this all afternoon and I'm truly stuck. I have several (x,y) co-ordinates. I would like to either create a formula or a macro I can use to give me what block they are in. In reality, I have about 24 possible blocks they can fall into and 160 data points. I am trying to group the data points based on their co-ordinates. Ie - which points would fall into block 15? Super mini example in case that wasn't too clear:

We have 4 squares/blocks with all positive vertices: (0,0), (1,0), (2,0),(0,1),(1,1),(2,1),(0,2),(1,2),(2,2) (Two squares on top of two squares, each with a length of 1). From left to right, top to bottom we'll lable each square from 1-4.
The point (1.5,1.5) is in block 2. I have a table filled with the ranges, min and max for both the x and y values (vertices). I'm looking for a formula to take my coordinate, say, (0.2,1.4), check to see if 0.2 is between 0 and 1, then check if 1.4 is between 1 and 2 and tell me that it's in block 1.

I found a formula using index and sumproduct for a similar scenario with only one parameter. This has two. Any ideas?

Please see the attached excel file for a table and sketch for better clarification.

Thank you!

Hi MarvinP,

Thank you so much for replying. I should have provided more info, my apologies - the formula works brilliantly for 4 blocks, but it's more or less hard coded. I was trying to modify it for many more blocks, but I don't know where to start. The real scenario is a total of 24 blocks. 6 across by 4 down, numbered in the same kind of order (1-6 along row 1, 7-12 along row 2, etc). Each block is a rectangle, 210m in length and 240m in height. But the x and y coordinates are enormous, not between 1 and 2 so they can't act as position vectors in the CHOOSE function. This is why I initially thought I would need perhaps INDEX or SUMPRODUCT to reference my table of vertices and block numbers.

I've attached a more detailed file.

Thanks again.

Originally Posted by MarvinP

Hi outatime1.21 and welcome to the forum,

Put this formula in G21 and pull it down. Or see attached.

Please Login or Register  to view this content.

Hi MarvinP -

I just noticed that! I fixed the values and modified the x,y coordinates by subtracting the minimum x,y coordinate and dividing by the respective length or height of the block. I think it works!

Thanks!