Look up the last non blank cell in a column and then return value of cell to the left a given number of cells in the same row

[jonathanb1989]

Hi, quick question. Super sorry if this has been asked already, I have been digging around for a while now and haven't been able to figure it out yet.

So what I'm trying to figure out is a way to look up the last non blank cell in a column and then shift over to the left of whatever row that is a certain number of cells and then return that value.

For example, I would like to make cell H8 spit this out after it looks over to the left 4 cells of the last non blank cell above it in that column.

09/08/2022 21:00:00

Another example, I would like to make cell F8 spit out

09/14/2022 09:00:00

That's not exactly what I'm trying to do but if I can figure that out I can figure it out from there I hope. Thanks in advance.

Flow Meters.xlsx
	A	B	C		E	F	G	H
1	Time	Date	Combined		Example Description	Example Description	Example Description	Example Description
2	9:00 PM	9/8/2022	09/08/2022 21:00:00		0.0000000	2542225.5	6110386.6	1274218
3	3:03 PM	9/13/2022	09/13/2022 15:03:00		269064.5400000
4	3:38 PM	9/13/2022	09/13/2022 15:38:00			2808459.3
5	5:00 AM	9/14/2022	09/14/2022 05:00:00
6	9:00 AM	9/14/2022	09/14/2022 09:00:00			2841740
7	9:16 AM	9/14/2022	09/14/2022 09:16:00				6133219.6
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=CONCATENATE(TEXT(B2,"mm/dd/yyyy")&" "&TEXT(A2,"hh:mm:ss"))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Time	Date	Combined		Example Description	Example Description	Example Description	Example Description
2	0.875	09/08/2022	08/09/2022 21:00:00		0	2542226	6110387	1274218
3	0.627083	9/13/2022	9/13/2022 15:03:00		269064.5
4	0.651389	9/13/2022	9/13/2022 15:38:00			2808459
5	0.208333	9/14/2022	9/14/2022 05:00:00
6	0.375	9/14/2022	9/14/2022 09:00:00			2841740
7	0.386111	9/14/2022	9/14/2022 09:16:00				6133220
8					9/13/2022 15:03:00	9/14/2022 09:00:00	9/14/2022 09:16:00	08/09/2022 21:00:00
Report

Cell Formulas
Range	Formula
C2:C7	C2	=CONCATENATE(TEXT(B2,"mm/dd/yyyy")&" "&TEXT(A2,"hh:mm:ss"))
E8:H8	E8	=LOOKUP(2,1/(E2:E7<>""),$C$2:$C$7)