Google Sheets – How to Return an Offset Cell Value for Last Occurrence

formulasgoogle sheets

I have a worksheet like this:

    ||  A     |  B    |  C  |
 ---++--------+-------+-----|
 ---++--------+-------+-----|
  1 ||  1     |  a    |     |
  2 ||  2     |  b    |     |
  3 ||  3     |  X    |     |
  4 ||  5     |  d    |     |
  5 ||  7     |  X    |     |
  6 ||  8     |  f    |     |
  7 ||  11    |  g    |     |
  8 ||        |       |     |
  9 ||        |       |     |

In cell D1, I need a formula that will return 7. It should return 7, because 7 is in the cell to the left of last occurrence of value X in column B.

I have been trying with VLOOKUP, OFFSET, MATCH and other built ins, but I can't do it.

I would prefer to do it with built in functions if this is possible.

Best Answer

=index(A:A;max(arrayformula(row(B:B)*(B:B="X"))))
Related Topic