Forum Discussion
List of names by least recent date in another column
Hi. I have a table of names and dates in Excel, for the last time people filled a certain role. Since it's a job that comes in teams, there are lots of duplicate dates. But I wan to get a list of t...
=IF(ROW()>7,"",VLOOKUP(COUNTIF($H$2:H2,H2)&H2,CHOOSE({1,2},$E$2:$E$19&$F$2:$F$19,$G$2:$G$19),2,FALSE))An alternative could be this formula along with two helper columns E and H in the example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
=IF(ROW()>7,"",LARGE($F$2:$F$19,ROW(A1)))This formula is in cell H2 and filled down.
=COUNTIF($F$2:F2,F2)This formula is in cell E2 and filled down.
Hey, this did the trick! Although I did have to swap LARGE for SMALL, since I'm looking for the least recent date. Can you explain how the CHOOSE function affects it?
Here's an explanation for VLOOKUP with CHOOSE.
VLOOKUP with multiple criteria advanced - Excel formula | Exceljet
