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]

How about

=IFNA(LOOKUP(2,1/((Q10:DA10<>"")*(Q10:DA10<>"Y")*(QA$6:DA$6<=Today())),Q10:DA10),"")

 

[buroh]

How about

=IFNA(LOOKUP(2,1/((Q10:DA10<>"")*(Q10:DA10<>"Y")*(QA$6:DA$6<=Today())),Q10:DA10),"")

Hi, thank you, but did not work.

It still returned the value of the column with a date in the near future, it seemed to have ignored the DA$6:QA$6<=TODAY() part of the formula.

I did try using the OR function but didn't like that either.

 

[Fluff]

Check that your dates in row 6 are real dates & not text.

 

[buroh]

Check that your dates in row 6 are real dates & not text.

Hi,

Yeh they are real dates

 

[Fluff]

What do you see if you change the format to General?

 

[buroh]

What do you see if you change the format to General?

44890 for 25/11/2022 as an example.

What should I change it too?

 

[Fluff]

That's fine, it's a real date.
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[buroh]

That's fine, it's a real date.
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you,

The add on will not work for me at work and IT are very hot on unverified add on's. I cannot find another way to upload my sheet.

okay I managed to get it to work, see below, thank you.

 

[buroh]

sample sheet.xlsx
	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	01/09/2022					SS
10	P01	03/11/2022									P01
11	P02	01/09/2022			P01		P02
12	0									P01
13	P01	01/05/2022		P01
14	P01	01/08/2022				P01
15	P01	03/11/2022									P01
16	P02	01/09/2022			P01		P02
17	P01									P01
18	P04	14/10/2022							P04
Sheet1

Cell Formulas
Range	Formula
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),""),"")
A10	A10	=IFNA(LOOKUP(2,1/((C10:CM10<>"")*(C10:CM10<>"Y")*(CM$11:PM$11<=TODAY())),C10:CM10),"")
A11	A11	=IFNA(LOOKUP(2,1/((C11:CM11<>"")*(C11:CM11<>"Y")*(OR(CM$11:PM$11<=TODAY(),CM$11:PM$11=TODAY()))),C11:CM11),"")
A12	A12	=IFNA(IF(B12>"",LOOKUP(2,1/((C12:K12<>"")*(C12:K12<>"Y")),C12:K12),""),"")
A13:A18	A13	=IFNA(LOOKUP(2,1/((C13:CM13<>"")*(C13:CM13<>"Y")),C13:CM13),"")

That's fine, it's a real date.
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.