Searching Data with Multiple Criteria

For my research I have large amounts of data that display x and y coordinates of particles from time lapse images. Using these coordinates we are then able to trace and visualize the trajectory of cell over time. Currently we are interested in viewing how neighboring cells are interacting with each other and if there are any signs of cooperativity among them when treated with certain growth factors.

I have written a macro that sorts the data and displays it as visible in the attachment posted. So far I have been able to write the macro to do exactly as I wish except now I've become stuck. In my spreadsheet column N displays the cell number of the identified neighboring cell/cells. In columns O and P I then am able to report the x and y coordinates of this neighboring cell respectively. However, for the next step of my analysis I would like to return the x and y coordinates of the neighboring cell in the next frame. I have been trying to use IF statements and different excel search functions in junction but I have yet to find a way to retrieve this data.

More information is explained in the spreadsheet as well as a visual representation of how the data is formatted but I understand this can be very confusing so please don't hesitate to ask questions.

IF the table pattern is constant as in the example then I would use the same principle the formula you use for "Nearest Neighbour" ie formula in Q6 would be -

Please Login or Register  to view this content.

The colouring part is a bit more complicated but could be solved by adding a "Nearest Neighbour Frame" column and using conditionalformatting, otherwise it's going to have to be code.

Edit. scratch all that just looked at the table patern and the above is a no go. I'll have a look and see what I can come up with regards code.

Originally Posted by Dave H9

OK I'll try again.

Possible solution is with INDEX&MATCH array formula. See what you think, I have populated column R.

Dave H

Dave,

Thanks a lot I was thinking that I would need to do an index&match array formula but I wasn't exactly sure how to execute. Your formula seems to work just as I wanted. I'll play with it and look at some data and see if I have any further questions. Thanks again.

Note: Also I just put the coloring in to help with my example a little bit. They do not actually need to be incorporated into the formula at all.