Look up a value and recall previous date that value appeared.

[SabbathXXL]

I searched for a solution to my problem and there's a couple that are related but I couldn't seem to get any to work for my situation. Doesn't appear to be that hard to figure out, but I don't have enough Excel-Fu to figure my way around this problem.

I'm creating a call log and want to set it up so that when I input a previously added value (TWO#), it will look up the last date that the TWO# was called.

In the attached spreadsheet, my desired values are highlighted in Yellow. Highlighted in Green is an example of the behavior I'm looking for. If a TWO# is referenced 3 times, I want the Previously Called values to be representative of the dates the calls were made.

For example:
1) On 3-1-15 I call TWO# 111
2) On 3-3-15 I call TWO# 111 again, previously called value = 3-1-15
3) On 3-5-15 I call TWO# 111 a third time, previously called value = 3-3-15
I want the value on line 2 to stay the same, regardless of any future calls made.

Any help with this would be great, thanks.

[AlKey]

Try this formula in F2 and copy it down

=IF(COUNTIF(E$2:E2,E2)=1,"New",INDEX(A$2:A$13,MATCH(E2,$E$2:$E$13,0)))

Format cells as Date

[SabbathXXL]

It's close, but not exactly what I'm looking for.

It's good when an item is marked as "New" and the first time it shows up as a duplicate, but in my sample worksheet, your formula leaves cell F13 as "3-3-15" when it should really be displaying "3-8-15" as 3-8-15 was the previous date in which TWO# 123 was called.

[AlKey]

Let's see if this one will do better

=IFERROR(IF(COUNTIF(E$2:E2,E2)=1,"New",IF(COUNTIF(E$2:E2,E2)=2,INDEX($A$2:$A$13,MATCH(E2,$E$2:$E$13,0)),INDEX($A$2:$A$13,IF(COUNTIF(E$2:E2,E2)=3,MATCH(E2,$E$2:$E$13))))),"")

[SabbathXXL]

It worked well in the example that I attached, but it still has some instability. If you erase all of the numbers that I put in for column E and replace them with any other numbers, it still has a hard time pulling the most recent date. Also, in your latest post, I see that you have it set up to work with up to 3 instances of a number, but there is a possibility of many more than 3 instances of a number to be in the entire spreadsheet. I would hate to think the only way to guarantee it works is to replicate an if statement for every reasonable chance of a certain TWO# appearing.

We're getting closer though. I've messed around with it a little bit, and I'll keep trying my hand at it, but would still appreciate any help you can give.

[AlKey]

This one seems fine with the data I have. Give it a try.

=IF(COUNTIF(E$2:E2,E2)=1,"New",SMALL(IF($E$2:$F$13=$E2,$A$2:$A$13),IF(COUNTIF(E$2:E2,E2)>2,2,COUNTIF(E$2:E2,E2)-1)))


***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

[skywriter]

Here's a non array formula solution, it requires that you have a non data row above your data. In this case your row 1 which has labels not data. Notice how part of the formula references row 1, this is not by accident.

Please Login or Register  to view this content.

[FlameRetired]

Here's another non-array.....same non-data row issue.

Please Login or Register  to view this content.

[SabbathXXL]

Sorry for having to revive an old thread, haven't been able to get on for a hot minute. Used skywriter's solution and it works flawlessly. Really appreciate the assistance!