Get most recent date if column contains specific text?

[Kazushin]

Hello -

This is my first post so hopefully I can get some help...

I am looking for a function that will grab the most recent date if a cell contains a specific text. For example:

1/1/2009 - Testing 1
2/2/2009 - Testing 2
3/3/2009 - Testing 1
4/4/2009 - Testing 3
5/5/2009 - Testing 4

The dates and Testing # are both in separate cells next to each other. I'm looking for a function that if any cells in the column contain "Testing 1" it will get the most recent date which in the example is 3/3/2009.

Thanks in advance.

[DonkeyOte]

If the dates are sorted in Ascending order then

=LOOKUP(2,1/(B1:B100="Testing 1"),A1:A100)

Else an Array is required

=MAX(IF(B1:B100="Testing 1",A1:A100))
committed with CTRL + SHIFT + ENTER

[Kazushin]

Thank you DonkeyOte. It worked perfectly.

Would you happen to know a function that could count the number of days that has past since the date it looked up until the current date? For example using the above function thats =LOOKUP, if the date was 05/15/09, it would display the number 3, since that was 3 days ago?

Thank you

[daddylonglegs]

You can just subtract the result of DonkeyOte's LOOKUP formula from today's date, i.e.

=TODAY()-LOOKUP(2,1/(B1:B100="Testing 1"),A1:A100)

Format result cell as general

[bobthebofifn]

Hello.

Sorry to bump this old thread. I want to do exactly the same thing as the OP except I can't get the solutions to work when I use * as a wildcard.

I've tried using a pivot table but obviously this throws out every unique value, which is not what I'm after.


Is there a formula that will let me use a wildcard?

[daddylonglegs]

Hello bobthe boffin,

Please start your own thread with a full explanation - in what way do you want to use a wildcard? Are the dates sorted?