Dynamic lookup and return value of last non-blank cell in that column

[Silverhorne]

Good morning,

I am working in Excel2019, I have been using index/match formulas to get most of what I need from my sheet but can't figure out how to get what I need with this one.
The below formula is incomplete but it shows the row I need from another tab:
=INDEX(Depth!$B$2:$ZZ$100,MATCH($C3,Depth!$B$2:$ZZ$2,0))
What I would like to do is find the last cell in this row that contains a value.
I am not sure if index/match will work for this, possibly some other lookup function that finds a dynamic value (in this case $C3) in a row (Depth!$B$2:$ZZ$2) on another tab and then displays the contents of the last populated cell in this column.

Any help is much appreciated!

 

[DanteAmor]

Check if you need this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		b	c	d	e	f	g	h	i	j	k	l	m	n	o	p
2		b2	c2	d2	e2	f2	g2	amor	i2	j2	k2	l2	amor	n2	o2	p2
3			amor
4		m10
5
6
7
8
9
10		b10	c10	d10	e10	f10	g10	h10	i10	j10	k10	l10	m10	n10	o10	p10
Sheet

Cell Formulas
Range	Formula
B4	B4	=LOOKUP(2,1/(B2:Z2=C3),B10:Z10)

 

[Silverhorne]

Thank you for the answer but not what I need. I need something that finds the last cell in a column but the column number is a dynamic value. Most of the formulas to find the last cell in a column with data require a specific column reference (A1:A100). Your method locks the results to row 10 when the results I need may be in row 7 in C, 4 in H, etc.

This formula gives the last cell in a column with data:
=LOOKUP(2,1/(A$6:A$8<>""),A$1:A$8)

This formula would be on another tab and reference a cell to match in the top row:
INDEX(Depth!$A$1:$I$8,MATCH($C3,Depth!$A$1:$I$1,0))

So if the reference cell had "P1-05" it would look for this in row A and return 5, if the reference cell was "P1-02" it would look for this in row A and return 4.
Depth:

P1-01	P1-02	P1-03	P1-04	P1-05	P1-06	P1-07	P1-08	P1-09
2	2	1	2	3	2	3	2	3
3	2	4		4	5	5	5	3
3	3			4	4	5	4	3
	4			4	5	4	5
				4		4
				5		5
				5

 

[JoeMo]

Assuming the reference cell is C3 on the formula sheet"

=LOOKUP(1E+307,INDEX(Depth!$A$1:$I$8,0,MATCH(C3,Depth!$A$1:$I$1,0)))

 

[Silverhorne]

Thank you so much JoeMo! I racked my brain for a good 5 hours trying to figure something out.

 

[JoeMo]

You are welcome - thanks for the reply.

 

[Peter_SSs]

From your sample data in post 3 it looks like this will indeed meet your needs, but just be aware that the formula will return the "last numeric value in the column", not necessarily the
"last non-blank cell in that column" as in your thread title or " the last cell ... that contains a value" as written in post 1.

 

[DanteAmor]

Sorry for the delay. Here is my answer, considering that the desired value may be a text or a number.

Book1
	A	B	C	D	E	F	G	H	I
1	P1-01	P1-02	P1-03	P1-04	P1-05	P1-06	P1-07	P1-08	P1-09
2	2	21	1	2	3	2	3	2	3
3	3	22	4		4	5	5	5	3
4	3	23			4	4	5	4	3
5		24			4	5	4	5
6		the last value			4		4
7					5		5
8					5
Depth

Array formulas:

Book1
	A	B	C	D
1
2
3			P1-02	P1-05
4			the last value	5
Sheet1

Cell Formulas
Range	Formula
C4:D4	C4	{=INDEX(Depth!$A$2:$I$8,MAX(IF(Depth!$A$1:$I$1=C3,IF(Depth!$A$2:$I$8<>"",ROW(Depth!$A$2:$I$8))))-1,MATCH(C3,Depth!$A$1:$I$1,0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[DanteAmor]

Another option with a "regular" formula

Book1
	A	B	C	D
1
2
3			P1-02	P1-05
4			the last value	5
Sheet1

Cell Formulas
Range	Formula
C4:D4	C4	=INDEX(Depth!$A$1:$I$8,SUMPRODUCT(MAX((Depth!$A$1:$I$1=C3)*(Depth!$A$1:$I$8<>"")*(ROW(Depth!$A$1:$I$8)))),MATCH(C3,Depth!$A$1:$I$1))

 

[Silverhorne]

Thank you all so much!
The original solution worked but I ran into a situation where I needed the array formula due to a letter added to the last cell.
I got 2 birds with 1 stone!! That made my day!