Find column number of last match in a row in sheets-CodePudding

In this table it's easy to find that column E is the first match for the value 3.

How do I find the column of the last match of 3 which will be column I

A   B   C   D   E   F   G   H   I   J   K   L
6   6   9   9   3   3   2   2   3   1   1   1

CodePudding user response：

Use this formula

=ArrayFormula(Substitute(Address(1,MAX(IF(REGEXMATCH(A1:L1,3&"")<>TRUE,,COLUMN(A1:L1))),4),"1",""))

CodePudding user response：

try:

=SUBSTITUTE(ADDRESS(2, XMATCH(3, A2:P2,, -1), 4), 2, )

=ADDRESS(2, XMATCH(3, A2:P2,, -1), 4)

=XLOOKUP(3, A2:P2, A1:P1,,, -1)