Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-22-2020, 06:16 AM
mandersen04 mandersen04 is offline Help with Index/Match Formula Windows 10 Help with Index/Match Formula Office 2016
Novice
Help with Index/Match Formula
 
Join Date: Sep 2018
Posts: 12
mandersen04 is on a distinguished road
Default 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
File Type: xlsm Copy of CRW Addresses for Matt.xlsm (22.8 KB, 3 views)
Reply With Quote
  #2  
Old 02-22-2020, 02:40 PM
BobBridges's Avatar
BobBridges BobBridges is offline Help with Index/Match Formula Windows 7 64bit Help with Index/Match Formula Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 674
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 02-22-2020, 06:31 PM
mandersen04 mandersen04 is offline Help with Index/Match Formula Windows 10 Help with Index/Match Formula Office 2016
Novice
Help with Index/Match Formula
 
Join Date: Sep 2018
Posts: 12
mandersen04 is on a distinguished road
Default

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!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index Match Formula ZetteS Excel 4 10-01-2018 02:16 AM
Help with Index/Match Formula mandersen04 Excel 2 09-03-2018 05:53 PM
index / match Formula dmcg9760 Excel Programming 1 11-08-2015 03:16 PM
Complex Formula using INDEX and Match needed OTPM Excel 5 05-23-2013 01:22 AM
Help with multiple match and index formula 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.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft