So a couple of weeks ago, I needed help with the GetDistance formula to calculate distances between addresses. I got that help (much appreciated), and I have since added a worksheet to my workbook, wherein I rank the distances for each address in relationship to another given address. I have attached a fake version of the file, with fake names, to show what it looks like.



The sheet 1 tab ranks the calculated distances from the Distance tab from smallest to largest (i.e. closest to farthest away), and right now I'm using an Index Match formula and a Small formula to capture the name of the person associated with the ranked distance. It works, but right now, the Small formula references a specific row in the Distance tab, meaning that if I ever sort the rows in the Distance tab in a different order, the rankings will be incorrect.

I know I need to reference the person's name in the formula and leave out the specific row reference, but unfortunately, Index Match formulas are not my strong suit. Any suggestions? Do I need an Array formula? What would it look like?

Thanks!

Attached Files

Copy of CRW Addresses for Matt.xlsm (22.8 KB, 9 views)