Formula to Return latest Status but must be more than 1 week ago

[tonywatsonhelp]

Hi Everyone,

Now in sheet "Status" Column C I have Dates and In Column E I have Statuses.

I want to be able to report what last weeks status was by looking down the list of dates and finding the most recent that is at lest 7 days old. and reporting that status

heres an example

	A	B	C	D	E	F
			Dates		Status	STATUS LOOK UP (WHAT i WANT)
			1/11/2017		Y	T
			12/12/2017		B	Explanation: so today is 21/04/2018 minus 7 days = 14/04/2018 find nearest date to that = 13/04/2018 Status = "T"
			11/01/2018		Y
			12/01/2018		Y
			24/02/2018		B
			15/03/2018		G
			1/04/2018		Y
			10/04/2018		B
			12/04/2018		G
			13/04/2018		T
			18/04/2018		Y
			21/04/2018		B

<tbody>
</tbody>

 

[MARZIOTULLIO]

	C​	D​	E​	F​
1​	Dates		Status
2​	11/1/2017​		Y
3​	12/12/2017​		B	T
4​	1/11/2018​		Y
5​	1/12/2018​		Y
6​	2/24/2018​		B
7​	3/15/2018​		G
8​	1/4/2018​		Y
9​	4/10/2018​		B
10​	4/12/2018​		G
11​	4/13/2018​		T
12​	4/18/2018​		Y
13​	4/21/2018​		B

<tbody>
</tbody>

F3=LOOKUP(TODAY()-7,C2:E13)

 

[markmzz]

Hi!

If the list is not sorted, try this:

=INDEX(E2:E13,MATCH(2,INDEX(1/(TODAY()-7>=C2:C13),)))

Markmzz

 

[tonywatsonhelp]

Thanks Marzio and Mark, both way work beautifully.

Tony

 

[markmzz]

Thanks Marzio and Mark, both way work beautifully.

Tony

You are welcome.

Markmzz