Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In excel, is there an excel formula I can use to populate Column B (in my example below) that will return the last row number that contains a specific string, before the current row?

The data is not contiguous.
For example, the data may be:

   |  A (value)    B (prev recent instance)
----------------------------------------------
 1 |  Mike
 2 |  John
 3 |  Mike         1
 4 |  Tony         
 5 |  Mike         3
 6 |  John         2

I'll then want to expand it to have Column C to show the earliest instance.

It seems very similar to this question: What is an excel formula that will return me the last row number of a cell within a range that contains specific data?, however that question and answer requires the data to be contiguous.

Edit: I would like the answer to be a Worksheet Formula, however if you think it's more appropriate to implement via VBA, please include that also in your answer with pros and cons of each approach.

share|improve this question
    
This would be much easier (as well as more intuitive) to do using VBA. – Breakthrough Jan 30 '12 at 12:46
    
Thanks for the suggestion Breakthrough, I've edited the question slightly to allow for this as an option so they can be compared. – Turgs Jan 30 '12 at 13:24
up vote 1 down vote accepted

To find earliest instance, mentioned below is the formula:-

=LOOKUP(2,1/(A:A=A2),ROW(A:A))

For your better understanding, below is the screenshot

enter image description here

share|improve this answer

Assuming your data starts at A1 try this "array formula" in B2

=IF(COUNTIF(A$1:A1,A2),MATCH(2,1/(A$1:A1=A2)),"")

confirmed with CTRL+SHIFT+ENTER and copied down

or you can use this non-array version

=IF(COUNTIF(A$1:A1,A2),MATCH(2,INDEX(1/(A$1:A1=A2),0)),"")

In Excel 2007 or later you can simplify with IFERROR, i.e. array version becomes

=IFERROR(MATCH(2,1/(A$1:A1=A2)),"")

For "1st instance" row in column C try this formula in C1 copied down

=MATCH(A1,A$1:A1,0)

Note: the formulas don't strictly return "row numbers" but the relative row numbers for your data, i.e. row 1 of your data returns 1 etc.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .