Return an offset cell value, where last occurrence in range is matched

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.

• It's a bit unclear. Why 7 and not 5 for the last occurrence of d? You should provide more data in your example – Lipis Mar 1 '12 at 11:54
• Do you mean "X" in your comment rather than "d"? If you can return 5 (the row number) I am sure I could suss it from there. It's the "last occurrence" part that I can't figure. – jon Mar 1 '12 at 12:04
• after my answer was submitted the additional requirement to do everything in one cell was added. That is an important requirement. The only other way is to use a pivot table – mhoran_psprep Mar 1 '12 at 15:28
• To me, the requirement was always in the question. see: "In cell D1". I didn't explicitly say "don't add columns" because I believe it to be clear enough. Column C is out of scope! If it's not possible in a single formula then at least I will find out. – jon Mar 1 '12 at 15:33

``````=index(A:A;max(arrayformula(row(B:B)*(B:B="X"))))
``````
• @Jon: perhaps you can flag it as such, so that other people can benefit. – Jacob Jan Tuinstra Dec 22 '12 at 10:48

In column C you will need to put into each cell the folowing formula:

``````    =if(countif(B:B,B1)=countif(B\$1:B1,B1),"Last_"&B1,"Not Last")
``````

Explanation:

• The first countif() tells you how many cells have that letter in Column B
• The second countif() tells you how many are between the top of the column and that row.
• If the two countif()'s give you the same number that row has the last occurrence for the letter so return "Last_"&letter. If they are not equal return "Not Last"

In column D use offset and match to return the value in column1 that has the value "Last_X" in column C. Note:vlookup() for this step would also work if the columns were in a different order.

• countif(B:B,B1) = 1. countif(B\$1:B1,B1) = 1. The first row would return "Last_X". – jon Mar 1 '12 at 15:10
• I think your formula should have been: `=if(countif(B:B,B1)=countif(B\$1:B1,B1),"Last_"&B1,"Not Last")`. Thanks for your help, but adding a column isn't the answer. I should have been more specific and said I require a single formula. – jon Mar 1 '12 at 15:14
• fixed typo in the original formula – mhoran_psprep Mar 1 '12 at 15:19
• after my answer was submitted the additional requirement to do everything in one cell was added. That is an important requirement. The only other way is to use a pivot table – mhoran_psprep Mar 1 '12 at 15:28
• To me, the requirement was always in the question. see: "In cell D1". I didn't explicitly say "don't add columns" because I believe it to be clear enough. Column C is out of scope! If it's not possible in a single formula then at least I will find out. – jon Mar 1 '12 at 15:34