Tell me more ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.
up vote 0 down vote favorite

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 occurance of value "X" in column B.

I have been trying with vlookup, offset, match and other builtins, but I can't do it.

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

Many thanks

Jon

share|improve this question
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

2 Answers

up vote 2 down vote 
=index(A:A;max(arrayformula(row(B:B)*(B:B="X"))))
share|improve this answer
Thank you tic, i have tested, and it works a treat! – Jon Mar 26 '12 at 13:44
@Jon: perhaps you can flag it as such, so that other people can benefit. – Jacob Jan Tuinstra Dec 22 '12 at 10:48
up vote 1 down vote

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.

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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