Search code examples
excelexcel-formulaarray-formulas

Excel formula for finding second earliest to the earliest date in a column

I have a data query in Excel that returns multiple rows of information for a single employee. The employee are sorted by an EmployeeID, and the data contains Pay Rates and Dates.

I have sorted the information by date descending in order to give me the first Pay Rate for an employee to put within a VLookUp for another sheet in my workbook. Now I need a formula to give me to next date in line so I can find the second earliest date in the range. Can this be done?

Data example:
1255  7.50   06/11/2012
1255  7.00   09/11/2011
1255  6.50   05/12/2011
2548  12.50  03/02/2012
2548  12.00  01/13/2012
Solution

  • Instead of VLOOKUP use INDEX/MATCH!

    Say your current formula is:

    =VLOOKUP(A1,Sheet1!A:C,3,0)

    This can be converted to

    =INDEX(Sheet1!C:C,MATCH(A1,Sheet1!A:A,0))

    It does the same thing (search in A for a match and return the result from column C). Now you can easily get the next match:

    =INDEX(Sheet1!C:C,MATCH(A1,Sheet1!A:A,0)+1)

    If you want to test if it's still the same employee (in column B), the formula is a bit more complicated:

    =IF(INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))=INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0)+1),INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0)+1),"Only one entry!")