Last Numeric Value in Filtered Table

[millertime571]

I have a table and one of the columns will have a lot of blank cells with some numeric values entered into it. I need to pull the last numeric value that is entered in the column.
The table will be filtered from time to time and I want the formula to update the last numeric value accordingly. In other words, if the table is filtered, I want the formula to only pull the last numeric value based upon the VISIBLE cells and to ignore the hidden cells that are filtered out.

The following equation will show the last numeric value in Column L:

=INDEX(L:L,MATCH(9.9999E+306,L:L))

However, when the table is filtered that equation stills shows the absolute last value, even if that cell is hidden. Is there a way to get the same result from the previous formula but to ignore hidden cells?

[ragulduy]

I don't believe you can test for if a cell is hidden without vba but you should be able to use what it is filtered by if that is constant?

[martindwilson]

this seems to work
=INDEX($A$1:$A$30,LOOKUP(2,1/INDEX((SUBTOTAL(104,OFFSET(A2:A30,ROW(A2:A30)-ROW($A$2),0,1))*ROW(A2:A30))>0,0),ROW(A2:A30)))

[XOR LX]

Great formula, Martin!

[millertime571]

Martin, this formula works great. I'm not gonna lie, I'm pretty impressed. I can't figure out why it works though. Would you mind giving me a brief explanation as to the methodology? The whole [array]*[array] thing is messing with my brain.

Thanks for the help.

[martindwilson]

no idea! i just adapted it from some formula snippets i had and kept trying different things untill it worked

[Izandol]

This may be a little simpler but must be array entered

=LOOKUP(9E+307,IF(SUBTOTAL(102,OFFSET(L2:L30,ROW(L2:L30)-ROW($L$2),0,1)),L2:L30,""))

OFFSET(L2:L30,ROW(L2:L30)-ROW($L$2),0,1)) creates an array of single cell ranges for each cell of L2:L30
SUBTOTAL(102,) returns 1 (coercible to TRUE) for each numeric cell that is visible thus the rest of the formula is only:
LOOKUP(9E307,IF(true/false array,L2:L30,""))

[daddylonglegs]

This formula will give you the last visible numeric value in L2:L100 (assuming hidden rows through filtering)

=LOOKUP(2,1/SUBTOTAL(2,OFFSET(L2,ROW(L2:L100)-ROW(L2),0)),L2:L100)

doesn't need array entry

The SUBTOTAL/OFFSET part returns a 1/0 value for every row, only 1 if it's visible and a number [2 in SUBTOTAL only counts numbers]

When you divide that array by 1 you therefore get 1s and errors. If you lookup 2 in that array it will always match with the last 1, and then LOOKUP gives you the corresponding value in L2:L100, hence giving you the last visible number

You can use the same approach for last number (not considering the filter), i.e.

=LOOKUP(9.99E+307,L2:L100)