1

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

Ravenerabnorm
  • 11
  • 1
  • If you're talking about structured tables then `Column1` is in cell `A1` and the last occurrence of `A` in `Column3` is in `C6`. Now the question is what do you want to reference (return): `A1:B6` (incl. headers) or `A2:B6`? Please add the clarifications and any other information by editing your post. – VBasic2008 Jul 20 '23 at 13:38
  • Thanks. I want to return A2:B6 so i just added +1 to the output of the formula that finds the required row. Alas, i am unable to input the formula into a named range. The following formula returns B6 but it doesnt seem to work when i put A$2$:Formula into the refers to input. =ADDRESS(MATCH(INDEX(FILTER(Table1[Column2],Table1[Column3]="A"), COUNTA(FILTER(Table1[Column2],Table1[Column3]="A")),0),(Table1[Column2],0)+1,2,1,1) – Ravenerabnorm Jul 20 '23 at 21:30
  • You can use any of my formulas and put them in a LAMBDA: `=LAMBDA(MyFormulaNoEqualSign)()`. Here is a less flexible version: `=LAMBDA(LET(tData,Table1,cData,INDEX(tData,,3),fRows,MAX(TOCOL(XMATCH(cData,{"A"})*ROW(tData)-@ROW(tData)+1,2)),IFERROR(TAKE(tData,fRows,2),"")))()`. This is what you use in the *Name Manager*. – VBasic2008 Jul 20 '23 at 22:09

2 Answers2

0

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.

Dominique
  • 16,450
  • 15
  • 56
  • 112
0

Reference a Range According to the Last Occurrence of a Value

enter image description here enter image description here

=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),""))
VBasic2008
  • 44,888
  • 5
  • 17
  • 28