# Placing coordinates to which grid it belongs (Combining IF with HLOOKUP or Index Match)

#### mvbarus

##### New Member
I have a set of data of coordinates X, Y, Z (Column A, B, C). They are located on a surface with grids consisting of 26 cells. I also have the information of those grids (Xc, Yc, Zc are the centre point of those grids, and X1 until Z2 are the corner points of them). (See attachment)

For each point, I want the columns D, E, F to return the value of Xc, Yc, Zc of the grid to which that point belongs to.
In other words, D2 will search in column I horizontally where the value of A2 is between X1 and X2, B2 is between Y1 and Y2, and C3 is between Z1 and Z2.

I tried writing the function below where it must fulfil those 3 conditions.
Excel Formula:
``````=IF(AND(A3>=MIN(\$L\$4,\$M\$4),A3<=MAX(\$L\$4,\$M\$4),
B3>=MIN(\$N\$4,\$O\$4),B3<=MAX(\$N\$4,\$O\$4),
C3>=MIN(\$P\$4,\$Q\$4),C3<=MAX(\$P\$4,\$Q\$4)),\$I\$4,0)``````

Here the function search only in Grid number 3 (because I manually know it belongs there). Therefore, it didn't search the set of Grid data. I know I should use either Hlookup or Index Match, but I just couldn't figure out where and how to put them.

#### Attachments

• Screenshot 2021-07-13 193520-min.jpg
218.3 KB · Views: 5

#### Marcelo Branco

##### MrExcel MVP
Try in D2 copied across and down
=INDEX(I\$2:I\$27,AGGREGATE(15,6,(ROW(I\$2:I\$27)-ROW(I\$2)+1)/((\$L\$2:\$L\$27<=\$A2)*(\$M\$2:\$M\$27>=\$A2)*(\$N\$2:\$N\$27<=\$B2)*(\$O\$2:\$O\$27>=\$B2)*(\$P\$2:\$P\$27<=\$C2)*(\$Q\$2:\$Q\$27>=\$C2)),1))

I found many duplicated results

M.

Last edited:

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### mvbarus

##### New Member
Try in D2 copied across and down
=INDEX(I\$2:I\$27,AGGREGATE(15,6,(ROW(I\$2:I\$27)-ROW(I\$2)+1)/((\$L\$2:\$L\$27<=\$A2)*(\$M\$2:\$M\$27>=\$A2)*(\$N\$2:\$N\$27<=\$B2)*(\$O\$2:\$O\$27>=\$B2)*(\$P\$2:\$P\$27<=\$C2)*(\$Q\$2:\$Q\$27>=\$C2)),1))

I found many duplicated results

M.
Yes, that works really well and solved my problem!
Thank you so much!
Indeed there are many duplicated results and I just want the formula to pick either one and it's not a problem which one.
So yes, that solved it. Thanks!

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Glad to help

M.

Replies
4
Views
78
Replies
4
Views
117
Replies
2
Views
274
Replies
3
Views
173
Replies
1
Views
811

1,141,816
Messages
5,708,756
Members
421,588
Latest member
Wawie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back