CODEWITHSUNDEEP
excelexcel-formulaminexcel-2016index-match
Jessica Smith
Jessica Smith

Reputation: 21

Excel 2016 - INDEX MATCH to return value based on smallest of multiple match results

Sorry, this is my first time posting. Hope I am able to explain clearly. Using Excel 2016. Hi, I have data as seen below, and I want to return the date based on the smallest of the matches returned.

1 Column A Column B Column C
2 date stock amount
3 05/25/24 242000 100.00
4 05/26/24 242001 200.00
5 05/27/24 242002 300.00
6 05/28/24 242000 400.00
7 05/29/24 242000 -100.00
8 05/30/24 242001 200.00
9 05/30/24 242001 250.00
10 05/31/24 242000 150.00

Match Value: Stock # 242000

=INDEX($A$3:$C$10,MATCH(242000, $B$3:$B$10, 0), 1)

This formula should return date 05/25/2024, since it is the first match result for that stock number. I need a formula that will return 05/29/2024, because that is the match for that stock number based on the lowest amount for that stock number in column C (Amount).

I think I am looking for an index match with multiple criteria the first being matching the stock #, the second being the lowest value for that stock number.

I have looked for a formula that will get me here, but haven't been able to find one. Please help!

Thanks so much!

Upvotes: 2

Views: 359

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

As posted in comments try the following formula using AGGREGATE() twice:

enter image description here

• Formula used in cell E2

=AGGREGATE(15,7,A2:A9/(C2:C9=AGGREGATE(15,7,C2:C9/(B2:B9=242000),1)),1)

Or, Can use with MIN() or SMALL() with the help of INDEX()+MATCH()

=INDEX(A2:A9,MATCH(1,(MIN((B2:B9=242000)*C2:C9)=C2:C9)*(B2:B9=242000),0))

Or,

=INDEX(A2:A9,MATCH(1,(SMALL((B2:B9=242000)*C2:C9,1)=C2:C9)*(B2:B9=242000),0))

NOTE: Formulas using MIN() or SMALL() would require to hit CTRL+SHIFT+ENTER while exiting the edit mode.

Upvotes: 3

Related Questions