Microsoft Office Forums Help with Index/Match Formula
 Register FAQ Search Today's Posts Mark Forums Read

#1
02-22-2020, 06:16 AM
 mandersen04 Windows 10 Office 2016 Novice Join Date: Sep 2018 Posts: 12
Help with Index/Match Formula

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, 3 views)
#2
02-22-2020, 02:40 PM
 BobBridges Windows 7 64bit Office 2010 32bit Expert Join Date: May 2013 Location: USA Posts: 674

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.
#3
02-22-2020, 06:31 PM
 mandersen04 Windows 10 Office 2016 Novice Join Date: Sep 2018 Posts: 12

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!

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post ZetteS Excel 4 10-01-2018 02:16 AM mandersen04 Excel 2 09-03-2018 05:53 PM dmcg9760 Excel Programming 1 11-08-2015 03:16 PM OTPM Excel 5 05-23-2013 01:22 AM ryanwood Excel 1 09-12-2012 07:53 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 04:47 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top