1

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
4
=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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.