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

Question

I have some data structured this way 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 that will give me the index of the rightmost column in this table, which has a cell corresponding to a specific value.

In this example, the rightmost column containing the value “1” is in column C. For “2,” it will be E. This column index is what I need.

I use column letters to match Excel, but the numeric index of the column is preferred.

I tried some other solutions for similar Excel problems found on the Internet, but they are not quite right.

Answer 1

. , , , .

, , . , 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)

IF , . , , .

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

, . .

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

Answer 2

, G1

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

. G3. G4

=MAX(G1:G3)

. , UDF,

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

=maxcolumn(A1:F3,2)

Answer 3

:

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