I have two tables of values with the same format. One table is the "Master" table, the other is a "Historical" table. Each row is a distinct record. I am trying to determine which (if any) records occur in the Historical Table that do not appear in the Master table.
The tables are sets of mining drill holes. Each drill hole has an ID, x and y coordinates, and two angle fields to describe its orientation.
Normally, this would be an easy exercise as I could just check to see that all Hole IDs found in the historical dataset were also found in the master dataset.
The problem is that some drill holes were renamed between then and now. My next option would be to find matching coordinates. The issue with that is that the coordinates of a given hole may vary by a few meters between then and now.
So far, I've set up a function that will take each hole in the "Historical" table and find the distance to the closest hole in the "Master" table. This function is as follows:
{=MIN(SQRT((('Historical'!D4-('Master'!$C$3:$C$2419))^2+('Historical'!E4-('Master'!$D$3:$D$2419))^2)))}
This array function looks at each hole in the Historical dataset (row 4 in this example), takes its x coordinate (column D) and it's y coordinate (column E) and performs a Pythagorean theorem calculation using every pair of coordinates in the Master tab. In concert with the MIN() function, this will find the minimum straight-line distance to a coordinate in the master table.
My question is, how can I find out which row in the Master table yielded those minimum-distance coordinates? The minimum value returned is a value calculated using values in the target row, so I can't just search for it directly.
I hope the question is clear and I've provided enough context.
