Using Excel table structured references to return a specific cell based on value in another cell

Question

I have an excel table, lets call it Table1. There are 3 columns, Column1 Column2 and Column3. Potentially there could be 30-50 rows. I would like to get the last cell index in Column2 based on the value in Column3. For example, I am looking for the last row in the table where Column3 = A. In the below example that would be cell 11 or B5. I would like to this using structured table references if possible

Column 1	Column 2	Column 3
Cell 1	Cell 7	A
Cell 2	Cell 8	B
Cell 3	Cell 9	B
Cell 4	Cell 10	B
Cell 5	Cell 11	A
Cell 6	Cell 12	B

I am attempting to make a dynamic named range that will update based on the value in column3 i.e. the range in this instance would be A1:B5.

Could anyone shine a line on how this can be done? I can get the last rows of any column and produce sub arrays of the table but am not able to successfully include Column3 = B as a modifier. Any help would be much appreciated.

Thanks

I've used INDEX (MATCH()) previously on basic excel formulats but am unable to make it work with table arrays

Answer 1

In this post, I've found how to find the last entry of an array.

I've combined this with the =FILTER() function in order to filter the rows on value "A":

=INDEX(FILTER(B1:B7,C1:C7="A","X"),
       COUNTA(FILTER(B1:B7,C1:C7="A","X")),
       0)

What does this all mean?

FILTER(B1:B7,C1:C7="A","X") (as the name says it) filters the range B1:B7 for all cases where the corresponding entries in column "C" (C1:C7) equal "A". This results in the following array:

Cell7
Cell11

Now we need to take the last entry of that resulting array. In order to do that, we need to know its length (hence the =COUNTA() function) and the =INDEX(), as explained in the linked post.

Answer 2

Reference a Range According to the Last Occurrence of a Value

=LET(tData,Table1,fCols,2,cData,Table1[Column 3],Crit,{"A"},
    fRows,MAX(TOCOL(XMATCH(cData,Crit)
        *ROW(tData)-@ROW(tData)+1,2)),
IFERROR(TAKE(tData,fRows,fCols),""))

=LET(tData,Table1,fCols,2,CritCol,3,Crit,{"A"},
    cData,INDEX(tData,,CritCol),
    fRows,MAX(TOCOL(XMATCH(cData,Crit)
        *ROW(tData)-@ROW(tData)+1,2)),
IFERROR(TAKE(tData,fRows,fCols),""))