Excel - Finding first non blank and then match to specific date

[Statto1970]

Hello

I am trying to match the first non blank entry (D2:H2) against a persons name (A2), then when I have found this bring back the date above the relevant cell (D1:H1).

For example if NAME 1 (A2) first non blank cell is D2, then I want the date contained in D1 to autofil into B2.

I hope this makes sense.
Thank you in advance.

 

[Toadstool]

You're welcome!
Let me explain further.

AGGREGATE has many functions and I'm using 15 (SMALL) and 14 (LARGE).
For the array argument I'm using the COLUMN numbers instead of just the cell references, so it will look through 4 to 9 (column D to column I) but I subtract 3 (column C) so the INDEX receives 1, 2, 3, 4, 5 or 6 to pick up the correct date from $D$1:$I$1.

I divide each column number by ($D2:$I2<>"") so if it is empty it gives FALSE or a logical zero which generates a zero divide which the AGGREGATE 6 option (Ignore error values) then ignores. If ($D2:$I2<>"") is TRUE I get a logical one which divided into the row number and returns the row I want.

The ",1" at the end is for the SMALLest and LARGEst results. i.e. if I use ",2" then I'd get the second non-empty cell with the 15 option and the penultimate non-empty cell with the 14 option.

 

[jasonb75]

Perhaps this?
=MINIFS(D$1:I$1,D2:I2,"<>")

With just a screen capture to go on, that could possibly be an incorrect answer.

I am trying to match the first non blank entry (D2:H2)

"<>" works with empty cells but not formula blanks.

decided to use my fallback of AGGREGATE with it's SMALL and LARGE options.

You don't need to use INDEX when the results are numeric, without it you can remove the COLUMN references from the array and make it more efficient.

Earliest date =AGGREGATE(15,6,$D$1:$I$1/($D2:$I2<>""),1)
Latest date =AGGREGATE(14,6,$D$1:$I$1/($D2:$I2<>""),1)