excel-formula

Finding the closest date in Excel

I have a spreadsheet with two sheets, They both have two columns: ID and Inspection Date.

I want to:

  1. Look at the first sheet for a row with a specific ID (xxxxx) and Date (x/xx/xxxx).

  2. Then, look at the second sheet to find matches with the same ID (xxxxx).

  3. Of all the resulting dates, find the one that is immediately before x/xx/xxxx.

Solution

  • You can use FILTER function for this to find all the dates that their ID match the ID in Sheet2 with dates before x/xx/xxxx. Then use MAX to find the closest match:

    =MAX(FILTER(Sheet2!B:B, (Sheet2!A:A=A2)*(Sheet2!B:B<B2)))

    Where A2 and B2 are ID and Date in the first tab.

    In case there are no matches, IFERROR can be used to handle it:

    =IFERROR(MAX(FILTER(Sheet2!B:B, (Sheet2!A:A=A2)*(Sheet2!B:B<B2))), "No Match")

    This will return No Match if there are no matches.