Returning next match to a equal value in a column

2
votes

I often need to search through columns to find the match to values and then return the according value. My issue is that INDEXand MATCHalways return the first value in the column.

EX. I got 7 car dealers and this is the sales last month. Oslo and Berlin sold the same ammount and INDEX(D:E,MATCH(B1,E:E,0),1)) in column C will return the first hit from column D.

A       B      C                   D         E
rank  Sales  Delaer
1     409    London         |     Tokyo     272
2     272    Tokyo          |     London    409
3     257    Hawaii         |     oslo      248
4     255    Stockholm      |     numbai    240
5     248    Oslo           |     Berlin    248
6     248    Oslo           |     hawaii    257 
7     240    Numbai         |     Stockholm 255

At the moment my best solution is to first find the row each value in B got in E with MATCH(B1,E:E,0) and add that to a new column (column F). Then I can add another formula in the next column, which is what I currently have to do: =IF(F2=F1;MATCH(F2;INDIRECT("F"&(1+F1)):$F$7;0))+F2

Is there a better approach at this?

1
excelexcel-formula

1 Answers

2
votes

In B2 use the following standard formula,

=IFERROR(LARGE(E$2:E$8, ROW(1:1)), "")

Fill down as necessary.

In C2 use the following standard formula,

=INDEX(D$2:D$8, AGGREGATE(15, 6, ROW($1:$7)/(E$2:E$8=B2), COUNTIF(B$2:B2, B2)))

Fill down as necessary.

        Oslo_twice

[Optional] - Repair the ranking in column A.

In A2 use the following formula,

=SUMPRODUCT((B$2:B$8>=B2)/(COUNTIFS(B$2:B$8, B$2:B$8&"")))

Fill down as necessary.

        repair_oslo