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]

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

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!

⚠ I would be slightly cautious. With either of the formulas from posts 8 or 9, if subsequently any rows might be added to the top of the 'Depth' sheet, those formulas will no longer return the correct result.

In row 4 below is another regular formula suggestion that is robust against such row insertion. I have used the same layout and ranges as in posts 8 & 9 with the same data in 'Depth'

Here is my result sheet with all 3 formulas returning the correct results:

Book1
	B	C	D
3		P1-02	P1-05
4	Post 12	the last value	5
5	Post 8	the last value	5
6	Post 9	the last value	5
Lookup

Cell Formulas
Range	Formula
C4:D4	C4	=INDEX(Depth!$A:$I,AGGREGATE(14,6,ROW(Depth!$A$1:$I$8)/((Depth!$A$1:$I$99<>"")*(Depth!$A$1:$I$1=C3)),1),MATCH(C3,Depth!$A$1:$I$1,0))
C5:D5	C5	{=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))}
C6:D6	C6	=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))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

.. and here it is again after a new row 1 has been added to 'Depth'

Book1
	B	C	D
3		P1-02	P1-05
4	Post 12	the last value	5
5	Post 8	0	#REF!
6	Post 9	0	#REF!
Lookup

Cell Formulas
Range	Formula
C4:D4	C4	=INDEX(Depth!$A:$I,AGGREGATE(14,6,ROW(Depth!$A$2:$I$9)/((Depth!$A$2:$I$100<>"")*(Depth!$A$2:$I$2=C3)),1),MATCH(C3,Depth!$A$2:$I$2,0))
C5:D5	C5	{=INDEX(Depth!$A$3:$I$9,MAX(IF(Depth!$A$2:$I$2=C3,IF(Depth!$A$3:$I$9<>"",ROW(Depth!$A$3:$I$9))))-1,MATCH(C3,Depth!$A$2:$I$2,0))}
C6:D6	C6	=INDEX(Depth!$A$2:$I$9,SUMPRODUCT(MAX((Depth!$A$2:$I$2=C3)*(Depth!$A$2:$I$9<>"")*(ROW(Depth!$A$2:$I$9)))),MATCH(C3,Depth!$A$2:$I$2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[DanteAmor]

.. and here it is again after a new row 1 has been added to 'Depth'

I don't have that problem in my file, I inserted several intermediate rows in the Depth sheet, I even deleted some rows and I don't have that problem that you mention.
Either way, I hope your formula works for the OP too ?

 

[Peter_SSs]

I inserted several intermediate rows in the Depth sheet,

Did you insert any new rows above row 1?

 

[DanteAmor]

Row 1 has the headings ?
My formula also has problems if the Depth sheet is deleted!

 

[Peter_SSs]

Row 1 has the headings

Yes, but a user might insert a new row 1 above that (eg for a new overall heading for the worksheet or to include a company logo etc)

 

[Silverhorne]

I used the =LOOKUP(1E+307,INDEX(Depth!$A$1:$I$8,0,MATCH(C3,Depth!$A$1:$I$1,0))) to extract the last numerical value from the depth data columns and then I needed to display the last value in the same column if it contained text so I used the {=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))}.
My sheet will always have the same header, although I had to mess around with the formula a bit because I have 3 rows containing date, time started, and time completed between the header row and the array.
The sheet is used to track how many times a steel pile is hit for every 250mm of embedment, each pile has a different length it needs to be driven and engineering needs to know many blows for the last 250mm. If the required blow count is not met, we go back 5 days later after it has set up and enter how much the pile moved in 10 hits into the very last column as "250mm" so the array formula pulls this data with text.
Sometimes it's nice to know what the effort is for! Thanks again!

 

[DanteAmor]

Thanks @Silverhorne for the whole context, let me know if you need anything else or any questions.