Excel formula - week number & day criteria outputs date

[kets23]

Hi

I'm after a formula that looks at a sheet (2nd table below), to display contents in Column G in the table below:

	A	B	C	D	E	F	G
	Name	Subject	Room	Time	Week Number	Day
1	R Jones	Maths	B12	09:00 – 11:00	1	Tuesday
2	T Wells	Science	B13	10:00 – 12:00	4	Friday
3	C Smith	History	B14	14:00 – 15:00	5	Wednesday
4	E Taylor	Geography	B15	11:00 – 12:00	6	Monday
5	B Leys	English	B16	13:00 – 14:30	8	Monday

<tbody>
</tbody>

In the example above, the formula would look at Column E week number 5 & Column F which is a Wednesday. The source data for this would be on a separate sheet (second table below) and the output I want to display in column G would be 24/10/2018.
I’m guessing I would need a Vlookup that looks at 2 criteria’s from the table below to display the output in column G but not sure how to construct this.

Any suggestions would be grateful.

Thanks


[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD] A[/TD]
[TD] B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Week[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]24/09/2018[/TD]
[TD]25/09/2018[/TD]
[TD]26/09/2018[/TD]
[TD]27/09/2018[/TD]
[TD]28/09/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]01/10/2018[/TD]
[TD]02/10/2018[/TD]
[TD]03/10/2018[/TD]
[TD]04/10/2018[/TD]
[TD]05/10/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]08/10/2018[/TD]
[TD]09/10/2018[/TD]
[TD]10/10/2018[/TD]
[TD]11/10/2018[/TD]
[TD]12/10/2018[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]15/10/2018[/TD]
[TD]16/10/2018[/TD]
[TD]17/10/2018[/TD]
[TD]18/10/2018[/TD]
[TD]19/10/2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]22/10/2018[/TD]
[TD]23/10/2018[/TD]
[TD]24/10/2018[/TD]
[TD]25/10/2018[/TD]
[TD]26/10/2018[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]29/10/2018[/TD]
[TD]30/10/2018[/TD]
[TD]31/10/2018[/TD]
[TD]01/11/2018[/TD]
[TD]02/11/2018[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7[/TD]
[TD]05/11/2018[/TD]
[TD]06/11/2018[/TD]
[TD]07/11/2018[/TD]
[TD]08/11/2018[/TD]
[TD]09/11/2018[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[TD]12/11/2018[/TD]
[TD]13/11/2018[/TD]
[TD]14/11/2018[/TD]
[TD]15/11/2018[/TD]
[TD]16/11/2018[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9[/TD]
[TD]19/11/2018[/TD]
[TD]20/11/2018[/TD]
[TD]21/11/2018[/TD]
[TD]22/11/2018[/TD]
[TD]23/11/2018[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10[/TD]
[TD]26/11/2018[/TD]
[TD]27/11/2018[/TD]
[TD]28/11/2018[/TD]
[TD]29/11/2018[/TD]
[TD]30/11/2018[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]11[/TD]
[TD]03/12/2018[/TD]
[TD]04/12/2018[/TD]
[TD]05/12/2018[/TD]
[TD]06/12/2018[/TD]
[TD]07/12/2018[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]12[/TD]
[TD]10/12/2018[/TD]
[TD]11/12/2018[/TD]
[TD]12/12/2018[/TD]
[TD]13/12/2018[/TD]
[TD]14/12/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaFrog]

This will calculate the date based on the DateValue as week 1. You could reference a cell with the week1 date instead of using DateValue.

=DATEVALUE("24/09/2018")+((E2-1)*7)+MATCH(F2,{"Monday","Tuesday","Wednesday","Thursday","Friday"},0)-1