CODEWITHSUNDEEP
excelformulaexcel-formula
joelpt
joelpt

Reputation: 5021

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.

Upvotes: 4

Views: 10799

Answers (4)

DKoontz
DKoontz

Reputation: 307

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

enter image description here

Full Tutorial Here

Upvotes: 0

Steven
Steven

Reputation: 928

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

Upvotes: 2

Dick Kusleika
Dick Kusleika

Reputation: 33145

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)

Upvotes: 3

Fionnuala
Fionnuala

Reputation: 91306

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

Upvotes: 2

Related Questions