return last populated cell based on lookup conditions

[buroh]

Hi All,

I need some help with a formula which is not working the way I want it too.

I have columns with dates and rows with values, see below:

my current formula in column M is: =IFNA(LOOKUP(2,1/((Q10:DA10<>"")*(Q10:DA10<>"Y")),Q10:DA10),"")

What it currently does is pick up the last cell in the row which is not blank, but will ignore a cell which has Y as a value.

But what I also need the formula to do is to return the value of the last populated cell of the column which has todays date or any date before todays date.

for an example the cell of row 12 column M would be blank because the last populated cell is of a date in the future.
but the value in row 10 of column N would be P01 as that is the last populated cell of todays date.

I have tried to use IF function, xlookup function but all I get is spill or error, especially because the formula doesn't like the < symbol when looking up the value e.g. <TODAY(). The IF or OR formula doesn't want to work, I am not sure what to do, any suggestions would be helpful.

Thanks,

 

[Fluff]

Thanks for that.
Once I correct your formula to look at the correct ranges, it works for me

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
7	Current Rev	Current Issue Date	01/04/2022	01/05/2022	27/06/2022	01/08/2022	01/09/2022	30/09/2022	14/10/2022	25/11/2022	03/11/2022
8			Stage 4	Stage 2	Stage 4	Stage 2	Stage 4	Stage 4	Stage 4	Stage 4	Stage 4
9	SS	44805					SS
10	P01	44868									P01
11	P02	44805			P01		P02
12										P01
13	P01	44682		P01
14	P01	44774				P01
15	P01	44868									P01
16	P02	44805			P01		P02
17	P01	44834						P01		P02
18	P04	44848							P04
Main

Cell Formulas
Range	Formula
A9:A18	A9	=IFNA(LOOKUP(2,1/((C9:CM9<>"")*(C9:CM9<>"Y")*(C$7:CM$7<=TODAY())),C9:CM9),"")
B9:B18	B9	=IFNA(IF(OR(XLOOKUP(A9,$C9:$K9,$C$7:$K$7,,0)<TODAY(),XLOOKUP(A9,$C9:$K9,$C$7:$K$7,,0)=TODAY()),XLOOKUP(A9,$C9:$K9,$C$7:$K$7,,0),""),"")

 

[buroh]

Thanks for that.
Once I correct your formula to look at the correct ranges, it works for me

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
7	Current Rev	Current Issue Date	01/04/2022	01/05/2022	27/06/2022	01/08/2022	01/09/2022	30/09/2022	14/10/2022	25/11/2022	03/11/2022
8			Stage 4	Stage 2	Stage 4	Stage 2	Stage 4	Stage 4	Stage 4	Stage 4	Stage 4
9	SS	44805					SS
10	P01	44868									P01
11	P02	44805			P01		P02
12										P01
13	P01	44682		P01
14	P01	44774				P01
15	P01	44868									P01
16	P02	44805			P01		P02
17	P01	44834						P01		P02
18	P04	44848							P04
Main

Cell Formulas
Range	Formula
A9:A18	A9	=IFNA(LOOKUP(2,1/((C9:CM9<>"")*(C9:CM9<>"Y")*(C$7:CM$7<=TODAY())),C9:CM9),"")
B9:B18	B9	=IFNA(IF(OR(XLOOKUP(A9,$C9:$K9,$C$7:$K$7,,0)<TODAY(),XLOOKUP(A9,$C9:$K9,$C$7:$K$7,,0)=TODAY()),XLOOKUP(A9,$C9:$K9,$C$7:$K$7,,0),""),"")

Thank you, it appears I got it my ranges wrong,

yep your formula works,

many thanks,

 

[Fluff]

Glad to help & thanks for the feedback.