Vlookup to find most recent row with a criteria.

**mlamb2005** · 09-05-2008, 04:05 PM

Hi all, I have a table that looks like the following:

Date Name Title Previous $ New $
6/20/2007 John A Analyst 1 $40,000 $40,000
7/28/2007 Mary R. Analyst 1 $42,000 $42,000
1/15/2008 Jamie G. Manager $65,000 $75,000
5/15/2008 John A. Analyst 2 $40,000 $50,000

Basically, it's a chronological (it will remain sorted, most recent at bottom) listing of all changes in employee pay within a department. So it allows management to track changes in titles, raises, etc.

I've received a request to view this data as "show me every employee's current status", which boils down to "return one line for each employee, and make sure it has the most recent date."

I started trying to do this with VLOOKUP, and I can find the overall most recent entry with no problem. However, I can't get it to return the most recent entry for "John A."

Any ideas?

Thanks!
Matt

**shg** · 09-05-2008, 04:09 PM

Welcome to the forum.

Why not sort descending by date, so the most recent data is on top? Then VLOOKUP will return the most recent data.

If you have to have it sorted ascending, you can use INDEX/MATCH.

**mlamb2005** · 09-05-2008, 04:16 PM

Hi there, thanks for the reply.

I think an expanded data set would make a better example, but it's not as simple as finding the most recent overall. Approximately 8 employees will have entries in the table, and I need to get exactly 8 lines, the most recent entry for each employee.

Since raises occur at random dates, it isn't precise to say that the 8 most recent entries will necessarily relate to the 8 employees at a 1-1 relationship.

If there is some way to do this with VLOOKUP, I'm overlooking it.

Thanks again.

Matt

**shg** · 09-05-2008, 04:27 PM

VLOOKUP(name, list, column, false) will return the result for the first match for the employee, which will be the most recent if the list is sorted descending by date, irrespective of the length of the list or how many times the names appear.

If you want to retrieve the date with a VLOOKUP as well, then move the names to the left-most column. Or, you can use an INDEX/MATCH formula to retrieve the date.

If the list must remain as-is, then you'd use INDEX/MATCH for everything.