How to find the index of the rightmost column in any table or array that has a cell matching a specified value.
Contents:
Formula
= LET(
goal, input_value,
range, table_or_array_range,
MAX(IFERROR(BYROW(range,LAMBDA(x,XMATCH(TRUE,INDEX(x=goal,),0,-1))),0)))By feeding an input value and a table or array range into this formula, it will return the rightmost column number in which the input value occurs.
Explanation
For any given table or array of data, it may be helpful to know where values are occurring within the array, and in some cases, know what is the rightmost column that a value appears in.
Here is a color coded picture to help illustrate what the formula is actually doing. For each value, the formula returns the rightmost column in which it appears. So for the purple 1s, the rightmost column is column 3, for the green 3s, the rightmost column is column 4.
The LET function is only used to simply the formula, it can be discarded and the "goal" and "range" can be replaced with your input value and table range or array.
The BYROW function uses the LAMBDA function and looks at each line in the set range to return the right-most column index number where the goal number is present.
For example, if we just run the BYROW piece of this formula:
= BYROW(table_range, LAMBDA(x,XMATCH(TRUE,INDEX(x=input_value,),0,-1)))
We will get a spilled range containing the rightmost column number that the value (1 in this case) occurs in, in each row.
The MAX function takes this spilled array and returns the highest number/rightmost indexed column.
The IFERROR function is used to catch any errors where the goal number isn't present, setting them to 0.
Note:
- Table names can also be substituted into this formula in place of a range. Next to the range argument, directly referencing a table name will also work. In this example, referencing the named table "RightTable".
=LET(
goal, I2,
range, RightTable,
MAX(IFERROR(BYROW(range,LAMBDA(x,XMATCH(TRUE,INDEX(x=goal,),0,-1))),0)))Example
How to Find the Rightmost Column Containing a Specific Value in a Table or Array
In this example, we have an array made up of 4 rows and 6 columns filled in with random numbers ranging from 1 to 4. We want to return the rightmost column for each value, listed in column I.
By putting our formula:
=LET(
goal, I3,
range, $B$3:$G$6,
MAX(IFERROR(BYROW(range,LAMBDA(x,XMATCH(TRUE,INDEX(x=goal,),0,-1))),0)))Alongside the input values in column I, we will return the rightmost column index number for each value.
