What is an excel formula that will return me the last row number of a cell within a range that contains specific data?

I have a spreadsheet that will default with values of "No". Over time, these values will be changed to "Yes".

I need a formula (no VB, please, VB is useless to me) that will get me the row number of the last "Yes" in a range of cells so that I can look up the value in that row number of another column to retrieve text to display.

Let's say I have this in one column (call it 1): "Yes", "Yes", "Yes", "No", "No"

And this in the referenced column (call it 2) 1, 1, 2, 2, 3

When all is said and done, INDIRECT(THE_FORMULA_HERE, 2) should retrieve me the value "2". And when all "Yes" are placed, it would change to "3", etc.

MetroidFan2002

Posted 2009-11-01T16:46:15.497

Reputation: 214

Answers

Assuming the data is contiguous (i.e. "Yes, Yes, Yes, No, No" but not "Yes, Yes, No, Yes, No") then you can use the following formula:

=INDIRECT("B" & (COUNTIF(A2:A6, "Yes")+1))

This is the data I used:

   | A      B
---------------
 1 | Yes    1
 2 | Yes    1
 3 | Yes    2
 4 | No     2
 5 | No     3

There are other ways to do it if you can add a sequence column, then use COUNTIF and VLOOKUP to do the same thing.

Richard Slater

Posted 2009-11-01T16:46:15.497

Reputation: 576

Thank you sir, that's exactly what I needed. Yes, the data is contiguous. – MetroidFan2002 – 2009-11-01T17:38:00.680

1How did you format the table like that that's nice :) – None – 2009-11-01T17:44:12.273

@metroidfan2002 not a problem, glad I could help – Richard Slater – 2009-11-01T19:22:53.760

@asksuperuser it is in a code block (four spaces before the first character of each line) then just use spaces to "tab" the characters to the correct position. Spent most of my formative programming years bugging people in IRC, that is the easiest way to display simple 2D datasets. – Richard Slater – 2009-11-01T19:24:56.207

Given an example of Column E containing the following:

  Row#  Value

     1. Yes
     2. No
     3. Yes
     4. Yes
     5. Yes
     6. Yes
     7. No
     8. No
     9. Yes
     10. No

You can use what's called an Array formula to accomplish this very easily. In this example, you would type the formula =MAX(ROW(E1:E10)*IF(E1:E10="Yes",1,0)) and press CTRL+SHIFT+ENTER to input the formula as an array formula Excel will add {} around the outside of the formula to indicate that it is an array formula.

CodeTrance

Posted 2009-11-01T16:46:15.497

Reputation: 29

I'd use INDEX rather than INDIRECT as it is non-volatile, but the concept would be almost identical if all the Yes answers are contiguous and not split by No's.

=INDEX(B1:B100,COUNTIF(A1:A100,"Yes"))

With 100 replaced by however many rows you really have

AdamV

Posted 2009-11-01T16:46:15.497

Reputation: 5 011