How to get data from the last data cell in a Table

[mikehk]

I am using this formula which works fine (if the set has complete data), I want to get data of the last available cell

=FILTER(Table1[DecEnd],Table1[Item]=$B$10)

Desired formula :
=FILTER(Table1[JanEnd, FebEnd MarEnd ....DecEnd],Table1[Item]=$B$10)

Need to filter JanEnd FebEnd MarEnd AprEnd MayEnd ....and so on..up to DecEnd.


Please see the attached sample file.

Thank you.

[Bo_Ry]

Please try
=LOOKUP(9^9,INDEX(Table1,MATCH(B9,Table1[Item],),))

or
=LOOKUP(9^9,IF(RIGHT(Table1[#Headers],3)="end",1/(1/INDEX(Table1,MATCH(B10,Table1[Item],),))))

[mikehk]

This works, thank you so much Bo_Ry, highly appreciate your help.

[mikehk]

please help me modify this formula that works fine retrieving the value from the last cell.....this time I want
to get the value from the first available cell

this formula works on the last cell
=LOOKUP(9^9,IF(RIGHT(Table1[#Headers],5)="start",1/(1/INDEX(Table1,MATCH(B15,Table1[Item],),))))

Please see the attached sample file.

Thank you.

[Bo_Ry]

Try

=--HLOOKUP("?*",IF(RIGHT(Table1[#Headers],5)="start",INDEX(Table1,MATCH(B15,Table1[Item],),)&""),1,)

[mikehk]

this works....thank you sooooo much