XLOOKUP ISSUES generating work schedules

[FreAnd]

Hi All

I'm trying to use XLOOKUP to fill a list with data from another list.

In list 1 I have the staff assigned to the different projects over the week.
In some cases two staff members will work on the same project.

In list 2 I want to show the day by day assignment for each staff member
according to list 1.

But I can't get XLOOKUP to work and I think it is because the look up array cant be over two columns (B5:C10).
because it works if I only set it in one.
Is there a better function to get this to work?

Any help or advice would be greatly appricated

Best regards
Fred

 

[fjns]

Hi, try this formula for I6
=XLOOKUP(H6,B6:B11,A6:A11,XLOOKUP(H6,C6:C11,A6:A11,""))

 

[fjns]

Sorry, my formula is wrong in #2.

This is the correct formula for I6:
=XLOOKUP(H6,B$5:B$10,A$5:A$10,XLOOKUP(H6,C$5:C$10,A$5:A$10,""))

Projects.xlsx

 

[Fluff]

Another option

Projects.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2
3
4		Monday	Monday	Tuesday	Tuesday				Monday	Tuesday
5	Project 1	Dave	Steve	Dave	Steve			Dave	Project 1	Project 1
6	Project 2	Steve						Steve	Project 1, Project 2	Project 1
7	Project 3							Mike		Project 5
8	Project 4
9	Project 5			Mike
10	Project 6
Munka1

Cell Formulas
Range	Formula
I5:I7	I5	=TEXTJOIN(", ",,IFERROR(TOCOL(IF($B$5:$C$10=H5,$A$5:$A$10,x),2),""))
J5:J7	J5	=TEXTJOIN(", ",,IFERROR(TOCOL(IF($D$5:$E$10=H5,$A$5:$A$10,x),2),""))

 

[fjns]

Hi, I modified the formula so that it can show more projects for a day.
The new formula for I6:
=TRIM(XLOOKUP(H6,B$5:B$10,A$5:A$10,"")&" "&XLOOKUP(H6,C$5:C$10,A$5:A$10,""))

Projects2.xlsx

 

[FreAnd]

Hi, I modified the formula so that it can show more projects for a day.
The new formula for I6:
=TRIM(XLOOKUP(H6,B$5:B$10,A$5:A$10,"")&" "&XLOOKUP(H6,C$5:C$10,A$5:A$10,""))

Projects2.xlsx

Ah,
Many thanks!
Great idea to combine two lookups, this works!!!
And can absolutely see the need to show more than one project for the day.
Again. thank you!

 

[FreAnd]

Another option

Projects.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2
3
4		Monday	Monday	Tuesday	Tuesday				Monday	Tuesday
5	Project 1	Dave	Steve	Dave	Steve			Dave	Project 1	Project 1
6	Project 2	Steve						Steve	Project 1, Project 2	Project 1
7	Project 3							Mike		Project 5
8	Project 4
9	Project 5			Mike
10	Project 6
Munka1

Cell Formulas
Range	Formula
I5:I7	I5	=TEXTJOIN(", ",,IFERROR(TOCOL(IF($B$5:$C$10=H5,$A$5:$A$10,x),2),""))
J5:J7	J5	=TEXTJOIN(", ",,IFERROR(TOCOL(IF($D$5:$E$10=H5,$A$5:$A$10,x),2),""))

Thank you my friend!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[fjns]

Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.