Last Cell to contain Specific Word then return this Second Cell

MrCat

New Member
Joined
Sep 25, 2017
Messages
10
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.
 

Some videos you may like

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
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,513
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top