Home / Functions / Search for earliest date in Excel list using vLookup

Search for earliest date in Excel list using vLookup

The ExcelHelpDesk team received the following request for help….

…..”Good morning,
I have a workbook that has two sheets: sheet1 contains two people (ID = 1 or 2), and each person has multiple records of DATE. Could you kindly inform me how to get the “earliest date” for each ID, so the table in Sheet1 will become the table in Sheet2? By the way, I am not familiar with EXCEL MACRO although I am a strong SAS SQL programer.

Where can I find the EXCEL SQL (free introduction material) for beginner, please?

Many thanks,
Helen
“……

The solution to this problem can be approached in a number of ways. In this post we will describe and method that uses built-in Excel functions to derive the result.

VLOOKUP (Excel Function)

Step 1

Select all values in Column A and B, then Sort the selection by Column A ascending and Column B oldest to newest. The Sort option dialog would look like

and the data would like the following after completing the Sort

Step 2

Now in Sheet2 we need to use an Excel function to lookup and find the first value in the list of values on Sheet1. We will use the VLOOKUP function to search the list on Sheet1 and when the first match is found return that value to the list on Sheet2.

In Sheet2 select cell B2, then select the Formulas Tab (Excel 2007) select Insert Function and choose VLOOKUP. Now specify the following values in the various options for the function.

The Col_index_num option indicates which field to return from the original list. The Range_lookup must be specified as FALSE, so that the first result is matched when the lookup is performed.

Step 3

When the function is completed for the first cell B2 and the result is returned, drag that function to the cell B3 and the VLOOKUP will perform the same lookup for the second record and return the earliest date from the Sheet1 list. When dragging the function to the next cell make sure that the Table_array parameter is set to Sheet1!A1:B8

Step 4

Finally we have the result showing the earliest dates for each of the ID’s in Sheet1

Another option to acheive the same result would be a VBA macro to search the list and return the earliest date.

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

About Excel Help Desk

Check Also

Calculate Number of Days in another Date Range

A custom function has been developed to calculate the Number of Days that one Date Range is within another. This can be very useful when calculating the overlapping periods.

2 comments

  1. I am a strong fomula writer but do not know how to write VBA macro, or create the VBA module.
    Can you provide me the information to do this?
    I have a 30,000 item list where column D contains Contract number (repeated multiple entries), column I contains deptfunc repeated multiple entries ( I need to find 554), and return the earliest date in column K containing date.
    contract condesc workord depfunc date
    CT40655 20971 572 18-May-12
    CT40655 20971 572 21-May-12
    CT40655 20971 572 22-May-12
    CT40734 21001 555 04-Oct-11
    CT40653 21012 521 20-Apr-11
    CT40653 21012 504 20-Apr-11
    CT40653 21012 555 10-Jun-11
    CT34234 21019 554 17-Mar-11
    CT34214 21027 552 06-Mar-11
    CT34214 21027 505 13-Apr-11
    CT34214 21027 553 06-Jun-11
    CT40728 21045 555 21-Feb-11
    CT40728 21045 554 15-Mar-11
    CT40728 21045 554 16-Mar-11
    CT40728 21045 554 17-Mar-11
    CT40728 21045 555 29-Mar-11
    CT40728 21045 555 30-Mar-11
    CT40728 21045 555 30-Mar-11
    CT33918 21050 552 06-Nov-11
    CT33919 21051 502 17-Jul-11
    CT34124 21054 553 01-Aug-11
    CT34124 21054 553 02-Aug-11