Reverse Lookup with No Row or Column Headings

[DarkDestiny]

So, I have been searching the internet for a way to do this and I cannot find anything. Most of the stuff I see deals with only column searching. Below is what I am trying to accomplish.

A B C D E
1 STK STK CTR CTR STK
2 L L L W
3 1 2 1 2 ??

I am looking for a way to search the previous instance of STK and increase the number below it. In the example above, I want to use an IF statement like this:

IF(AND(D1="CTR",D2="W"), I want to find the previous entry of STK (B1), and increase the number below it (2 --> 3).

Is there a way to do this?

Thanks...

[Special-K]

Cells cannot change other cells.
Cells can read other cells and produce results based on other cells, but they can't change them.

Use VBA to change the values in cells.

[DarkDestiny]

Excel Issue.gif

I dont want to change it. I want to increase the number found and insert into another cell. Is that still not possible?

[ChemistB]

IF it always increases by 1, starting in B3 copied to the right

=COUNTIF($A$1:A$1, B$1)+1
Does that work for you?

[DarkDestiny]

Nope, that gives me a value of 2. Its hard to me to fathom that Excel doesnt have a way to look backwards for the first occurrence of a word. Basically, if the top column is named CTR and row 2 has a W, then go find the first STK before entering the CTR phase (which is B1:B3, in this instance.). Use the number at B3, increase it by 1 and enter at E3.

Updated.gif

[ChemistB]

No, I said to put that formula into B3 and copy to the right. If you do that, then the formula in E3 will be

=COUNTIF($A$1:D$1, E1)+1

[DarkDestiny]

Hmm... that looks like it's going to work.

I will play with this some more and see where it leads me.

Thanks for the knowledge!