I have two data sets. One is the "planned" coordinate (X & Y, i.e. 2 numbers in two columns) and the other is an "actual" coordinate in random order.
I would like to match the "actual" coordinates to the "planned" coordinates. This to me sounds like the index and match function with multiple criteria. The challenge for me is writing a formula where the "actual" coordinates my be slightly different. See data example:
PLANNED ACTUAL
ID X Y Z X Y Z
600 4755.97 1825.91 15.68 4756.68 1625.42 14.91
601 4756.47 1805.63 15.80 4756.50 1725.53 15.55
602 4756.56 1785.55 15.76 4756.87 1565.46 15.45
603 4756.51 1765.56 15.68 4756.77 1605.43 14.85
604 4756.47 1745.64 15.61 4756.86 1585.47 14.90
605 4756.52 1725.52 15.54 4756.55 1685.60 15.43
606 4756.50 1705.53 15.51 4755.98 1825.90 15.67
607 4756.54 1685.56 15.43 4756.91 1545.52 15.61
608 4756.77 1665.56 14.98 4756.85 1525.51 15.85
609 4756.74 1645.58 15.32 4747.11 1525.35 15.55
610 4756.70 1625.43 14.90 4747.17 1545.39 15.43
611 4756.77 1605.41 14.86 4747.02 1565.35 15.32
612 4756.87 1585.43 14.89 4756.77 1665.56 14.97
613 4756.88 1565.47 15.44 4756.74 1645.58 15.31
614 4756.93 1545.52 15.61 4756.49 1705.53 15.51
615 4756.89 1525.52 15.85 4756.47 1745.63 15.61
616 4747.15 1525.37 15.54 4756.57 1785.55 15.75
617 4747.17 1545.40 15.39 4756.50 1765.56 15.68
618 4747.06 1565.37 15.30 4756.48 1805.65 15.80
Over all I would like to search the "actual" X & Y and possibly Z and match it to the closest "planned" XYZ resulting with assigning the "actual" XYZ the corresponding I.D. so I calculate the difference between the two.
Thanks for the help.
Bookmarks