Excel : Corresponding value of minimum value(multiple minimum values)-CodePudding

I want to fetch values based on unique values from Column A i.e. for L1 & L2

ex : for L1, I need to find minimum date in column C i.e. D1 and then its corresponding value in column D i.e. A & for L2, I need to find minimum date in column C i.e. D1 and then its corresponding value in column D in this case i.e. C

I tried with

INDEX(D:D, MATCH(IF(MIN(A:A="L1", C:C, "")), C:C, FALSE)

its giving me results of the first match..... As L2's min date is D1, its taking the first occurrence of D1 and give me result as "A" but I need "C""

CodePudding user response：

Below assumes your dates are actually dates, and not tokens as you show in your screenshot. (If they are tokens, you will need to split the text and number so you can obtain an accurate minimum date)

There are a number of ways to do this.

If you have Microsoft 365:
- INDEX, SORT, FILTER.
- INDEX, MATCH, MINIFS
- as suggested by @JvDV: INDEX, SORTBY

For example, with your data table above made into a Table named Status:

H2: =UNIQUE(Status[State])
I2: =INDEX(SORT(FILTER(Status,Status[State]=H2),3),1,4)

or, as suggested by @JvDv below:

I2: =INDEX(SORTBY(Status[Status],Status[State]=H2,-1,Status[Date],),1)

and fill down

Or, if you only want to enter a single formula in H2, you can try:

H2: =BYROW(UNIQUE(Status[State]),LAMBDA(arr,INDEX(SORT(FILTER(Status,Status[State]=arr),3),1,4)))

and the results will SPILL down

If you have an earlier version of Excel, you can list the State in Column H, generating that list in a variety of ways, and use the following array formula in I2 (and fill down as needed):

I2: =INDEX(Status[Status],MATCH(MIN(IF(Status[State]=H2,Status[Date],"")),IF(Status[State]=H2,Status[Date],""),0))

Depending on your version of Excel, you may need to enter/confirm this array formula, by holding down ctrl shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.