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)

I spent some time staring at the formula in Sheet1 and thinking it looks unnecessarily complicated. Maybe I'm mistaken about that; I'm still staring.

But I can answer at least the immediate question: If you're worried what happens if you sort the rows on the Distance sheet, consider changing the formula in Sheet1!A: Instead of putting literal names in there, put "=Distance!RC1". That is, refer to whatever is on the same row in col A of the Distance sheet. Then if you sort the rows in Distance, the names in Sheet1 will be sorted too, and still yield correct results.

If you want the names in Sheet1 to remain in the same order no matter what you do in Distance, then...hm. (Falls silent awhile, thinking.) I think it might be simpler to do it using VBA. But then I always think that.

That worked really nicely, thank you! I don't really need the Sheet1 tab to be in the same order, so long as the rank columns are accurate. I just wanted to make sure activity on one sheet wouldn't screw up another. Thanks again!