Finding the 2nd, 3rd, 4th … MATCH()

From Daily Does of Excel

Finding the 2nd, 3rd, 4th … MATCH():

Excel’s MATCH() function will return the relative location of the first occurrence that satisfies the match criterion (the lookup_value) within the specified array (the lookup_array). Most often, this is what you want. For instance, if you have duplicate entries, returning the first match is no different than returning the last match. However, if you have unique entries with the duplicated criterion, how do you return all the unique values?

For example, you have a list of the 122 major professional sports teams (32 NFL, and 30 MLB, NBA, and NHL) sorted alphabetically in Column D. In Column E you have code that returns the length of the team name, i.e. =LEN(D1). Your data might look like this:

	D	E
1	Anaheim Ducks	13
2	Arizona Cardinals	17
3	Arizona Diamondbacks	20
4	Atlanta Braves	14
5	Atlanta Falcons	15
6	Atlanta Hawks	13
7	Baltimore Orioles	17
8	Baltimore Ravens	16
9	Boston Bruins	13
10	Boston Celtics	14

For no good reason we want to create a descending sort on name length. You can do it from the Sort selection, but we also want to keep the alpha-sort. In F1 you put =LARGE($E$1:$E$122,ROW()) and fill down. The ROW() function will cause the return of the first largest, then the second largest etc. Then you want to know how far down Column E those descending numbers fall, so that you can Index the name list that amount. In G1 put the code =MATCH(F1,$E$1:$E$122,0) and fill down. You’ll see from the data that the longest name is 29 letters, and it’s in ROW(52).

	D	E	F	G
1	Anaheim Ducks	13	29	52
2	Arizona Cardinals	17	22	30
3	Arizona Diamondbacks	20	22	30
4	Atlanta Braves	14	22	30
5	Atlanta Falcons	15	21	42
6	Atlanta Hawks	13	21	42
7	Baltimore Orioles	17	21	42
8	Baltimore Ravens	16	20	3
9	Boston Bruins	13	20	3
10	Boston Celtics	14	20	3

You can begin to see the problem: There are three teams with a 22-letter name, three with a 21-letter name, and at least 3 (in fact 13) with a 20-letter name. If in H1 we put =INDEX(D:D,G1) and filldown, we get:

	D	E	F	G	H
1	Anaheim Ducks	13	29	52	Los Angeles Angels of Anaheim
2	Arizona Cardinals	17	22	30	Columbus Blue Jackets
3	Arizona Diamondbacks	20	22	30	Columbus Blue Jackets
4	Atlanta Braves	14	22	30	Columbus Blue Jackets
5	Atlanta Falcons	15	21	42	Golden State Warriors
6	Atlanta Hawks	13	21	42	Golden State Warriors
7	Baltimore Orioles	17	21	42	Golden State Warriors
8	Baltimore Ravens	16	20	3	Arizona Diamondbacks
9	Boston Bruins	13	20	3	Arizona Diamondbacks
10	Boston Celtics	14	20	3	Arizona Diamondbacks

This is exactly what we wanted to avoid. So, using Col(H) now as a helper column, in H1 put =G1. In H2 put

• =IF(G2<>G1,G2,H1+MATCH(F2,INDIRECT(“E”&(H1+1)&”:E122″),0))

and fill down. If the value did not repeat, just copy it over; and if it didn’t, we’re making a “sliding range” that starts one row after the start of a repeated pair, ends at the end of the data, and “slides” down Column E. The sliding range is made by the INDIRECT() function. It builds the range one row down (H1+1) from the first of a repeating match. We add H1 to that result, and we have our index down Column D. This may be better seen in H3, adjacent to G3, where the first repeat starts.

• =IF(G3<>G2,G3,H2+MATCH(F3,INDIRECT(“E”&(H2+1)&”:E122″),0))

The IF() is false. We are looking for the relative location of the lookup_value (22) in a new lookup_range (E31:E122). It’s 33 rows in. We add the original 30 to that, and the index is now 63. A similar thing happens in H4. H5 is G5 copied over. In Column I, I1 =INDEX(D:D,H1), filled down. The resulting table:

	D	E	F	G	H	I
1	Anaheim Ducks	13	29	52	52	Los Angeles Angels of Anaheim
2	Arizona Cardinals	17	22	30	30	Columbus Blue Jackets
3	Arizona Diamondbacks	20	22	30	63	Minnesota Timberwolves
4	Atlanta Braves	14	22	30	95	Portland Trail Blazers
5	Atlanta Falcons	15	21	42	42	Golden State Warriors
6	Atlanta Hawks	13	21	42	83	Oklahoma City Thunder
7	Baltimore Orioles	17	21	42	89	Philadelphia Phillies
8	Baltimore Ravens	16	20	3	3	Arizona Diamondbacks
9	Boston Bruins	13	20	3	49	Jacksonville Jaguars
10	Boston Celtics	14	20	3	53	Los Angeles Clippers

The names are now sorted by length, by alphabet. INDIRECT() is volatile, so there is a penalty for this. For extra credit, and no peeking, what’s the shortest name?

…mrt

©¿©¬