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.

 

[jasonb75]

As long as they are proper dates

=MINIFS(D$1:I$1,D2:I2,1)

 

[Toadstool]

Hi Statto1970,

Here's one solution using Excel 2016:

Statto1970.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	First Date Wanted	Last Date Wanted	Fri 01/01/1960	Fri 08/01/1960	Fri 15/01/1960	Fri 22/01/1960	Fri 29/01/1960	Fri 05/02/1960
2	Name 1	Fri 01/01/1960	Fri 05/02/1960	1		1	1		1
3	Name 2	Fri 08/01/1960	Fri 05/02/1960		1				1
4	Name 3	Fri 15/01/1960	Fri 05/02/1960			1	1	1	1
5	Name 4	Fri 08/01/1960	Fri 08/01/1960		1
6	Name 5	Fri 08/01/1960	Fri 15/01/1960		1	1
7	Name 6	Fri 05/02/1960	Fri 05/02/1960						1
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=INDEX($D$1:$I$1,,MATCH(1,D2:I2,0))
C2:C7	C2	=INDEX($D$1:$I$1,LOOKUP(2,1/(D2:I2<>""),COLUMN(D1:J1))-COLUMN($C$1))

 

[Peter_SSs]

@Statto1970
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Statto1970]

Thank you. These work perfectly. Much appreciated.

If I wanted to have it recognise other numbers or text in the cells rather than just 1's, how would I change the formula?

 

[jasonb75]

recognise other numbers or text

number or text or numbers and text?

With some formulas, methods for text do not work with numeric values and vice versa. The method for numbers can also vary depending on whether possible entries are positive or negative and whether or not 0 is a valid entry.

 

[Peter_SSs]

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

 

[Toadstool]

My Last Date Wanted already supported either text or numeric. For First Date Wanted I was going to give you an array formula but decided to use my fallback of AGGREGATE with it's SMALL and LARGE options.

Statto1970.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	First Date Wanted	Last Date Wanted	Fri 01/01/1960	Fri 08/01/1960	Fri 15/01/1960	Fri 22/01/1960	Fri 29/01/1960	Fri 05/02/1960
2	Name 1	Fri 01/01/1960	Fri 05/02/1960	Goat		1	Dog		Cat
3	Name 2	Fri 08/01/1960	Fri 05/02/1960		8766				9922
4	Name 3	Fri 15/01/1960	Fri 05/02/1960			A	B	C	D
5	Name 4	Fri 08/01/1960	Fri 08/01/1960		1.2
6	Name 5	Fri 08/01/1960	Fri 15/01/1960		Mole	Rabbit
7	Name 6	Fri 05/02/1960	Fri 05/02/1960						Big Dog
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=INDEX($D$1:$I$1,,AGGREGATE(15,6,COLUMN($D2:$I2)-COLUMN($C2)/($D2:$I2<>""),1))
C2:C7	C2	=INDEX($D$1:$I$1,,AGGREGATE(14,6,COLUMN($D2:$I2)-COLUMN($C2)/($D2:$I2<>""),1))

 

[Statto1970]

number or text or numbers and text?

With some formulas, methods for text do not work with numeric values and vice versa. The method for numbers can also vary depending on whether possible entries are positive or negative and whether or not 0 is a valid entry.

It will most likely be both numbers and text. For example it could be 5C or R5 or just 5.

We are still working out the best way to enter and display our data.

Thank you.

 

[Statto1970]

My Last Date Wanted already supported either text or numeric. For First Date Wanted I was going to give you an array formula but decided to use my fallback of AGGREGATE with it's SMALL and LARGE options.

Statto1970.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	First Date Wanted	Last Date Wanted	Fri 01/01/1960	Fri 08/01/1960	Fri 15/01/1960	Fri 22/01/1960	Fri 29/01/1960	Fri 05/02/1960
2	Name 1	Fri 01/01/1960	Fri 05/02/1960	Goat		1	Dog		Cat
3	Name 2	Fri 08/01/1960	Fri 05/02/1960		8766				9922
4	Name 3	Fri 15/01/1960	Fri 05/02/1960			A	B	C	D
5	Name 4	Fri 08/01/1960	Fri 08/01/1960		1.2
6	Name 5	Fri 08/01/1960	Fri 15/01/1960		Mole	Rabbit
7	Name 6	Fri 05/02/1960	Fri 05/02/1960						Big Dog
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=INDEX($D$1:$I$1,,AGGREGATE(15,6,COLUMN($D2:$I2)-COLUMN($C2)/($D2:$I2<>""),1))
C2:C7	C2	=INDEX($D$1:$I$1,,AGGREGATE(14,6,COLUMN($D2:$I2)-COLUMN($C2)/($D2:$I2<>""),1))

Thank you. I am slowly beginning to understand.