# INDEX match or similar function to return a character value in the same row as the minimum date for an ID that has multiple entries



## rwille (Dec 29, 2022)

I have a list of transactions, where the same account ID can appear multiple times in the same column depending on how many transactions they have made. Im trying to somewhat dedup the dataset.

I already have the min and max date of transaction history, but would like to include which type of transaction occurred on the particular min or max date for that account ID. 


How do I fill out column H and I with a formula to get the desired result?


----------



## AhoyNC (Dec 29, 2022)

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Put formula in cell H2 and drag down and across as needed.

Book1ABCDEFGHI1uniquelDInvoce datetype of salelist of dedupedlffsmin datemax dateMINdatesaletypeMAXdatesaletype2abc1/1/2022mabc7/21/20211/1/2022im3def10/14/2021bdef3/1/202110/14/2021ib4ghi10/1/2021ighi10/1/202110/1/2021ii5jkl9/25/2021ijkl9/25/20219/25/2021ii6abc7/21/2021imno6/5/20216/5/2021ii7mno6/5/2021ipqr5/1/20215/1/2021bb8pqr5/1/2021b9def3/1/2021iSheet2Cell FormulasRangeFormulaH2:I7H2=INDEX($C$2:$C$9,MATCH($E2&"/"&F2,$A$2:$A$9&"/"&$B$2:$B$9,0))Press CTRL+SHIFT+ENTER to enter array formulas.


----------



## rwille (Dec 29, 2022)

AhoyNC said:


> Try:
> This is an array formula that must be entered with CTRL-SHIFT-ENTER.
> Put formula in cell H2 and drag down and across as needed.
> 
> Book1ABCDEFGHI1uniquelDInvoce datetype of salelist of dedupedlffsmin datemax dateMINdatesaletypeMAXdatesaletype2abc1/1/2022mabc7/21/20211/1/2022im3def10/14/2021bdef3/1/202110/14/2021ib4ghi10/1/2021ighi10/1/202110/1/2021ii5jkl9/25/2021ijkl9/25/20219/25/2021ii6abc7/21/2021imno6/5/20216/5/2021ii7mno6/5/2021ipqr5/1/20215/1/2021bb8pqr5/1/2021b9def3/1/2021iSheet2Cell FormulasRangeFormulaH2:I7H2=INDEX($C$2:$C$9,MATCH($E2&"/"&F2,$A$2:$A$9&"/"&$B$2:$B$9,0))Press CTRL+SHIFT+ENTER to enter array formulas.


this works! THANK YOU


----------



## AhoyNC (Dec 29, 2022)

You're welcome. Thanks for the feedback.


----------

