# Last Cell to contain Specific Word then return this Second Cell

#### MrCat

##### New Member
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.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Rick Rothstein

##### MrExcel MVP
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.

Last edited:

#### MrCat

##### New Member
Hi Rick,

Thanks that seemed to work! Have a nice day.

##### MrExcel MVP
Also, just enter:

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

Replies
12
Views
101
Replies
7
Views
48
Replies
6
Views
99
Replies
4
Views
55
Replies
1
Views
219