Using INDEX/MATCH functions with duplicate values

rastus

Board Regular
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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Jerry Sullivan

MrExcel MVP
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
ABCDE
1AaronBobConDanEd
23-Jan-135-Jan-133-Jan-131-Jan-132-Jan-13
335412
4
5
6DanEdAaronConBob
71-Jan-132-Jan-133-Jan-133-Jan-135-Jan-13
Sheet

rastus

Board Regular
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.

Replies
1
Views
146
Replies
2
Views
148
Replies
0
Views
229
Replies
3
Views
105
Replies
7
Views
142

1,195,924
Messages
6,012,341
Members
441,691
Latest member
starlightmuse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back