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