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

[mvbarus]

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.

=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.

Please help.

 

[Marcelo Branco]

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.

 

[mvbarus]

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]

You are welcome. Glad to help

M.