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