If partial match show certain word

[Thunder76]

Really hoping someone will be kind enough to help me with this formula.

I have a rota schedule that certain people are on for certain days of the week. For instance if person 1 worked Monday then i would their name to appear in column D.

I would ideally like it to check column B4 and if it says Monday then should display 'Person 1' in Column D4. If B4 has Tuesday within the long date field then it should show 'Person 2' in Colmn D4. There is a different person for each of the seven days.

Any help would be amazing!

thanks
Simon

 

[CA_Punit]

But where is the name list which is associated with each day??

 

[etaf]

are these dates formatted to also show the day - or just Text entries
if just Text
this will put person 1 in the field
="person "&WEEKDAY(VALUE(LEFT(E2,10)),2)

You could have a List of names and use that to lookup the name
Using a Weeknumber on the DATE or by using the DAY text

Book1
	E	F	G	H	I	J	K
1
2	01/08/2020 Saturday		Saturday P		1	Monday Person
3	02/08/2020 Sunday		Sunday P		2	Tuesday P
4					3	Wednesday P
5					4	Thursday P
6					5	Friday P
7					6	Saturday P
8					7	Sunday P
9
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=VLOOKUP(WEEKDAY(VALUE(LEFT(E2,10)),2),$I$2:$J$8,2,FALSE)

 

[Thunder76]

Wayne
Thank you so much for responding so quickly.

This worked perfecly (the Vlookup).

Thanks again - you have saved my evening
Simon

 

[etaf]

you are welcome