Optimal lookup formula to return a list of available names

[emergguy]

I am looking for the best formula to assist in scheduling "available" vs "not available" lists for particular dates

Book1
	A	B	C	D	E
1	Who?	19-Jan	20-Jan	21-Jan
2	Anderson
3	Arjang	Contract	Contract
4	Bainsy	Off		Off
5	Becha
6	Bhimano
7	Brind
8	Byrne	SelfSched	SelfSched	SelfSched
9	Cain
10	Corter
11
12		Available	Available	Available
13
14		Anderson
15		Becha
16		Bhimano
17		Brind
18		Cain
19		Corter
20
21		Not Available	Not Available	Not Available
22
23		Arjang
24		Bainsy
25		Byrne
26
27
Sheet1

• A1:D10 are auto-populated by a form.
• I am looking to reproduce the results of column B such that if an entry under a particular date is blank, the corresponding name goes into the 'available' list (ie under B12)
• Conversely, if an entry under a particular date is not blank, the corresponding name goes into the 'not available' list (under B21 or similar)
• Ideally these generated lists are dynamic

I have been searching many options, but I am having difficulty making XLOOKUP work.

Any help is greatly appreciated as the Form results involve 100 names and 100 dates.

Thanks!

 

[bebo021999]

Book1
	A	B	C	D
1	Who?	19-Jan	20-Jan	21-Jan
2	Anderson
3	Arjang	Contract	Contract
4	Bainsy	Off		Off
5	Becha
6	Bhimano
7	Brind
8	Byrne	SelfSched	SelfSched	SelfSched
9	Cain
10	Corter
11
12		Available	Available	Available
13		Anderson	Anderson	Anderson
14		Becha	Bainsy	Arjang
15		Bhimano	Becha	Becha
16		Brind	Bhimano	Bhimano
17		Cain	Brind	Brind
18		Corter	Cain	Cain
19			Corter	Corter
20
21		Not Available	Not Available	Not Available
22		Arjang	Arjang	Bainsy
23		Bainsy	Byrne	Byrne
24		Byrne
25
26
27
28
Sheet1

Cell Formulas
Range	Formula
B13:D19	B13	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$10)/(B$2:B$10=""),ROWS($1:1))),"")
B22:D28	B22	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$10)/(B$2:B$10<>""),ROWS($1:1))),"")

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D
1	Who?	19-Jan	20-Jan	21-Jan
2	Anderson
3	Arjang	Contract	Contract
4	Bainsy	Off		Off
5	Becha
6	Bhimano
7	Brind
8	Byrne	SelfSched	SelfSched	SelfSched
9	Cain
10	Corter
11
12		Available	Available	Available
13
14		Anderson	Anderson	Anderson
15		Becha	Bainsy	Arjang
16		Bhimano	Becha	Becha
17		Brind	Bhimano	Bhimano
18		Cain	Brind	Brind
19		Corter	Cain	Cain
20			Corter	Corter
21
22
23		Not Available	Not Available	Not Available
24
25		Arjang	Arjang	Bainsy
26		Bainsy	Byrne	Byrne
27		Byrne
28
Data

Cell Formulas
Range	Formula
B14:B19,C14:D20	B14	=FILTER($A$2:$A$10,B2:B10="")
B25:B27,C25:D26	B25	=FILTER($A$2:$A$10,B2:B10<>"")
Dynamic array formulas.

 

[emergguy]

Book1
	A	B	C	D
1	Who?	19-Jan	20-Jan	21-Jan
2	Anderson
3	Arjang	Contract	Contract
4	Bainsy	Off		Off
5	Becha
6	Bhimano
7	Brind
8	Byrne	SelfSched	SelfSched	SelfSched
9	Cain
10	Corter
11
12		Available	Available	Available
13		Anderson	Anderson	Anderson
14		Becha	Bainsy	Arjang
15		Bhimano	Becha	Becha
16		Brind	Bhimano	Bhimano
17		Cain	Brind	Brind
18		Corter	Cain	Cain
19			Corter	Corter
20
21		Not Available	Not Available	Not Available
22		Arjang	Arjang	Bainsy
23		Bainsy	Byrne	Byrne
24		Byrne
25
26
27
28
Sheet1

Cell Formulas
Range	Formula
B13:D19	B13	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$10)/(B$2:B$10=""),ROWS($1:1))),"")
B22:D28	B22	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$10)/(B$2:B$10<>""),ROWS($1:1))),"")

Beautiful.
Clearly I need to learn more about these functions and then will try and dissect the formula.
Thank you!

 

[emergguy]

Another option

Fluff.xlsm
	A	B	C	D
1	Who?	19-Jan	20-Jan	21-Jan
2	Anderson
3	Arjang	Contract	Contract
4	Bainsy	Off		Off
5	Becha
6	Bhimano
7	Brind
8	Byrne	SelfSched	SelfSched	SelfSched
9	Cain
10	Corter
11
12		Available	Available	Available
13
14		Anderson	Anderson	Anderson
15		Becha	Bainsy	Arjang
16		Bhimano	Becha	Becha
17		Brind	Bhimano	Bhimano
18		Cain	Brind	Brind
19		Corter	Cain	Cain
20			Corter	Corter
21
22
23		Not Available	Not Available	Not Available
24
25		Arjang	Arjang	Bainsy
26		Bainsy	Byrne	Byrne
27		Byrne
28
Data

Cell Formulas
Range	Formula
B14:B19,C14:D20	B14	=FILTER($A$2:$A$10,B2:B10="")
B25:B27,C25:D26	B25	=FILTER($A$2:$A$10,B2:B10<>"")
Dynamic array formulas.

Whoa.
I didn't know about the FILTER function - it seems very useful.
Love it. Much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback.
In future please do not mark your post as the solution. You need to mark the post that helped you.