Last Cell to contain Specific Word then return this Second Cell

[MrCat]

Hi Everyone,

I'm trying to work on a formula which has me stumped.

I have a table which contains Names in one Column(A), and Dates in a second Column(B).
I only want to return column B DATE if the Column A Name is Bob
But it has to be the last Row that has Bob in it

i.e.
A B
Name Date
Alice 2018-01-02 14:30
Bob 2018-01-14 14:30
John 2018-01-16 13:30
John 2018-02-01 13:30
Peter 2018-02-14 00:13
Bob 2018-02-15 13:13
Alice 2018-02-15 14:13


Now even though there is a Bob with the Date 2018-01-14 14:30 , I only want to return 2018-02-15 13:13

I've tried a few different formulas but cant quite manage it. I was most recently seeing if I could work using the Index command to get the last row to contain Bob, but it wasn't working.

=IF(A:A="Bob",INDEX(B:B,COUNTA(B:B)))

Any help at all is appreciated.

 

[Rick Rothstein]

Give this array-entered** formula a try (change the range references to match your data layout, but note that I would not recommend use whole column references)...

=MAX(IF(A2:A8="Bob",B2:B8))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[MrCat]

Hi Rick,

Thanks that seemed to work! Have a nice day.

 

[Aladin Akyurek]

Also, just enter:

=LOOKUP(9.99999999999999E+307,1/($A$2:$A$8="bob"),$B$2:$B$8)