Excel – an excel formula that will return me the last row number of a cell within a range that contains specific data

microsoft excelworksheet-function

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.

Best Answer

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.

Related Question