I have a list of serial numbers that have different scan dates:

Serial # Started Ended Count L50099 28-02-14 11:50 28-02-14 15:20 1 L50099 25-11-15 15:55 25-11-15 19:25 2 L50098 03-03-16 11:30 03-03-16 15:00 1 L50099 01-07-16 13:37 01-07-16 17:07 3

How do I return the cell of the previously Ended date?

For example, how do I return 03/03/16 3:00 PM (row C, Ended column) into row D in the Prev column? And the rest for all the others?

My actual data will have a heap of other serial numbers and dates in between, this is just an example.

I'm trying index/match and vlookup but it's fixated onto the same column, not one previous to it.

EDIT: Can I use INDEX MATCH MATCH to get the date for the serial # of the count, minus 1, which is the date of the previous count?

The formula for the Count column is =COUNTIF(A$2:A2,A2)


In D2 put this array formula:

=IFERROR(INDEX($C$1:C1,MATCH(2,IF($A$1:A1=A2,1))),"No Previous")

Being an array it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode. If done correctly then Excel will put {} around the formula.

Then copy down.

enter image description here

  • I'm confused as to why you're starting at row 1, the column titles? – BBking Jul 10 '16 at 15:38
  • Because it needs to be confirmed as an array, does that mean it can't also be used within another formula? – BBking Jul 10 '16 at 15:54
  • @BBking I start at row one because we need to look at all the rows above the current one. The second part of the range is not absolute and there for will increase as the formula is filled down from A1 to A2 to A3 ... As to if it can be used in another formula depends on the other formula. – Scott Craner Jul 10 '16 at 16:08
  • I've got a count column. Maybe that can help? – BBking Aug 2 '16 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.