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

mvbarus

New Member
Joined
Jul 13, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

Please help.
 

Attachments

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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mvbarus

New Member
Joined
Jul 13, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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! 😊
 

Forum statistics

Threads
1,147,482
Messages
5,741,409
Members
423,658
Latest member
Kumaradas

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top