Home » Questions » Computers [ Ask a new question ]

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

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".

Asked by: Guest | Views: 75
Total answers/comments: 2
Guest [Entry]

"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."
Guest [Entry]

"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"