Using INDEX/MATCH functions with duplicate values

[rastus]

Hi

I receive data (A1:E2) which is sorted alphabetically accordng to name:

Aaron Bob Con Dan Ed
3-Jan-13 5-Jan-13 3-Jan-13 1-Jan-13 2-Jan-13

I use the SMALL function to sort the data in date order e.g: A6= =SMALL($A$2:$E$2,1) = 1/1/13, B6= =SMALL($A$2:$E$2,5) = 2/1/13, etc:

1-Jan-13 2-Jan-13 3-Jan-13 3-Jan-13 5-Jan-13

I then use the INDEX & MATCH functions to place the names under the sorted dates e.g. A7= =INDEX($A$1:$E$1,MATCH(A6,$A$2:$E$2,0)) = "Dan", B7= =INDEX($A$1:$E$1,MATCH(B6,$A$2:$E$2,0)) = "Ed", etc:

1-Jan-13 2-Jan-13 3-Jan-13 3-Jan-13 5-Jan-13
Dan Ed Aaron Aaron Bob

The problem I have with this formula is that if there is a duplicate date (i.e. 3/1/13), the INDEX/MATCH formula returns a second "Aaron" rather than "Con".

Does anyone know how to modify this formula so it will return "Con" and not a second "Aaron"? Please note that duplicate dates may recur across the row.

I have searched this and other forms but the formulas always assume the data is in multiple rows rather than multiple columns.

Thanks

R

 

[Jerry Sullivan]

Hi Rastus,

You could use Rank and Countif to place unique rankings in Row 3 for each Date.
From there, the Index-Match is straight forward.

Excel Workbook
	A	B	C	D	E
1	Aaron	Bob	Con	Dan	Ed
2	3-Jan-13	5-Jan-13	3-Jan-13	1-Jan-13	2-Jan-13
3	3	5	4	1	2
4
5
6	Dan	Ed	Aaron	Con	Bob
7	1-Jan-13	2-Jan-13	3-Jan-13	3-Jan-13	5-Jan-13
Sheet

 

[rastus]

Hi JS411

Thanks for that!

It took me a few go's to get right. It's so important to get the $ signs in the right place.