'Excel formula to find the rightmost column containing a value in a table

I have some data structured like this in an Excel spreadsheet:

    A B C D E F

1   1 1 2 x 2 3
2   1 1 1 2 2 3
3   3 3 3 3 4 4

I am trying to formulate an Excel formula which will give me the index of the rightmost column in this table which has a cell matching a specific value.

In this example, the rightmost column containing the value '1' is in column C. For '2' it would be E. That column index is what I'm after.

I use column letters to be Excel-consistent, but a numeric column index is preferable.

I have tried some other solutions for similar Excel problems found online, but they don't quite have the right behavior.

Solution 1:^[1]

Here is a way to do it with formulas. I'll show how to do it with a few different formulas to show the steps of the logic, and then put them together into one big formula.

First, use one formula per column to see if the target value is in the column. For example in column A:

=COUNTIF(A1:A100,Goal)
=COUNTIF(B1:B100,Goal)
...
(where Goal can be a hardcoded search value,
 or a named range where you type your query)

Then, add IF statements to these formulas to translate this into column numbers. If the query is present in the column, show the column number, else show zero.

=IF(COUNTIF(A1:A100,Goal)>0, 1, 0)
=IF(COUNTIF(B1:B100,Goal)>0, 2, 0)
...

Finally, add a formula to grab the maximum column number from the prior formulas. This will equal the rightmost column with your query value in it.

=MAX( IF(COUNTIF(A1:A100,Goal)>0, 1, 0), IF(COUNTIF(B1:B100,Goal)>0, 2, 0), ...)

Solution 2:^[2]

If you want to use helper columns, you can put this formula in G1

{=MAX((COLUMN(A1:F1)*(A1:F1=2)))}

That's array entered. Fill down to G3. In G4, put

=MAX(G1:G3)

Then repeat for each number. If you don't want helper columns, you can write a UDF like this

Public Function MaxColumn(rInput As Range, vValue As Variant) As Long

    Dim rFound As Range

    Set rFound = rInput.Find(vValue, rInput.Cells(1), xlValues, xlWhole, xlByColumns, xlPrevious)

    If Not rFound Is Nothing Then MaxColumn = rFound.Column

End Function

Which you call like

=maxcolumn(A1:F3,2)

Solution 3:^[3]

How about:

Function FindCol(ToFind)
Dim r As Range
Dim rfind As Range
Dim rfound As Range
Set r = ActiveSheet.UsedRange

For i = r.Columns.Count To 1 Step -1
    Set rfind = r.Columns(i)
    Set rfound = rfind.Find(ToFind)
    If Not rfound Is Nothing Then
        Result = rfound.Column
        Exit For
    End If
Next

FindCol = Result

End Function

Solution 4:^[4]

Here's a solution using the new LAMBDA and BYROW functions.

The BYROW looks at each line in a set range and returns the right-most column index number where the goal number is present. The IFERROR is used to catch any errors where the goal number isn't present, and the MAX is used to return the highest number/rightmost indexed column.

=LET(
goal, $H$1,
range, A1:F3,
MAX(IFERROR(BYROW(range,LAMBDA(x,XMATCH(TRUE,INDEX(x=goal,),0,-1))),0)))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Steven
Solution 2	Dick Kusleika
Solution 3	Fionnuala
Solution 4	DKoontz

'Excel formula to find the rightmost column containing a value in a table

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]

Solution 4:^[4]