|Per the Excel help files:|
MATCH(lookup_value, lookup_array, [match_type])
"The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range."
A B C
1 21 33 42
=MATCH(33,A1:C1,0) would return 2 because 33 is in the 2nd position of the range.
=MATCH(33,D11:D13,0) would also return 2 because 33 is in the 2nd position of the range.
MATCH doesn't care about the actual Excel Column or Row because it returns the relative position of the lookup_value within the specified lookup_array, e.g A1:C1 or D11:D13
OK, so let's look at the formula I suggested, and break it down.
=MATCH(lookup_value, lookup_array, [match_type])
The COLUMN() function returns the Column number in which it resides. e.g. In Column B, COLUMN() will return 2. In Column C, =COLUMNS() will return 3, etc.
Therefore in Column B, LARGE($B$3:$I$3,COLUMN()-1) will be equivalent to:
=LARGE($B$3:$I$3,1) which will return the longest Travel Time in B3:I3. As you drag that across, the value that COLUMNS() will return will increment by 1, giving you:
=LARGE($B$3:$I$3,2) in Column C
=LARGE($B$3:$I$3,3) In Column D
LARGE returns the actual Travel Time from B3:I3, but we want the Departure Location, right? To solve that we use MATCH.
Since MATCH returns the relative position of the lookup_value in the lookup_array, the entire formula returns the relative position of the LARGEest (longest) Travel Time first, then the next LARGEest (next longest), etc.
You'll note that it is not returning the actual number from Row 2 (your departure locations) instead it is returning the relative position of the values in B3:I3 which just happen to match your Departure Locations.
Now, the problem with MATCH is that it returns the relative position of the first match it finds. Therefore, if you have the same value in e.g. relative position 2 and relative position 5, MATCH will return 2 for both of them.
The way to fix that is to alter the values that you are trying to match so that there will never be a 2 or more values equal to each other. That can be done with what is referred as a Helper Row (or Column).
For ease of explanation, I'll suggest a Helper Row.
Insert a Row above your Travel Times. In the new B3 enter this formula and drag it across to I3:
What this will do is decrease your Travel Times by a tiny amount by using the COLUMN() function so that no Travel Times will be equal to each other. If the denominator is large enough, you won't even see the difference but internal to Excel all of the numbers will be different.
Now, use the MATCH formula on the Helper Row and you'll get unique relative positions (or Departure Locations) for each Travel Time.
You can hide the Helper Column if you don't want to see it.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.