Table of contents

  1. Lookup a value and find max date
    1. How to enter an array formula
    2. Explaining array formula
    3. Download excel file
  2. Lookup all values and find max date
  3. Lookup and find last date using multiple conditions
  4. Lookup and find latest date on multiple sheets
  5. Lookup and find latest date, return another value on same row
  6. Functions in this article

Lookup a value and find max date

The picture below shows you values in column B (B3:B9) and dates in column C (C3:C9). The formula in cell F4 lets you search for value and return the latest date in an adjacent or corresponding column for that value.

Update, 2017-08-15! Added a regular formula.

Formula in cell F4:

=MAX(INDEX((C3=A8:A14)*B8:B14,))

Array formula in F4:

=MAX(IF(C3=A8:A14, B8:B14))

Formula in cell F4 (Excel 2016):

=MAXIFS(C3:C9,B3:B9,F2)

Watch a video where I explain the formulas

Recommended article:

Finding the nearest date in a range of dates using excel formula

Array formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) Recommended articles: How to enter an array formula Select cell E3 Type […]

Comments(42) Filed in category: Dates, Excel

How to create an array formula

  1. Double click cell C5
  2. Copy / Paste above array formula
  3. Press and hold Ctrl + Shift simultaneously
  4. Press Enter
  5. Release all keys

The formula changes and now begins and ends with a curly bracket, don't enter these characters yourself. They appear automatically.

Recommended article:

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

Back to top

Explaining array formula in cell C5

You can follow along if you select cell C5 and go to tab "Formulas" on the ribbon and then click "Evaluate Formula" button. Click "Evaluate" button, shown on the dialog box, to move to next step.

Step 1 - Find values equal to lookup value

C3=A8:A14

becomes

"EE"={"AA";"CC";"EE";"BB";"EE";"VV";"EE"}

and returns

{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}

Step 2 - Convert boolean values to corresponding dates

IF(C3=A8:A14, B8:B14)

becomes

IF({FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE} ,B8:B14)

becomes

IF({FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE} , {40152; 40156; 40323; 40068; 40149; 40312; 40195})

and returns

{FALSE;FALSE;40323;FALSE;40149;FALSE;40195}

Step 3 - Return the largest value

=MAX(IF(C3=A8:A14, B8:B14))

becomes

=MAX({FALSE;FALSE;40323;FALSE;40149;FALSE;40195})

and returns 40323 formatted as 2010-05-25.

Download excel file for this tutorial.

Lookup value and return max date.xlsx

Back to top

Lookup all values and find latest (earliest) date

The following formula looks in column C for the most recent date for each value in column B.

Formula in cell D3:

=IF(MAX(INDEX((B3=$B$3:$B$14)*$C$3:$C$14,))=C3,"Latest","")

Array formula in cell D3:

=IF(MAX(IF(B3=$B$3:$B$14, $C$3:$C$14))=C3, "Latest", "")

How to create an array formula

Formula in cell D3:

=IF(MAXIFS($C$3:$C$14,$B$3:$B$14,B3)=C3,"Latest","")

Watch a video explaining the formula above

How to copy array formula

  1. Copy cell C2
  2. Select cell range C3:C8
  3. Paste

Download excel file

Lookup value and return max date.xlsx

Back to top

Lookup and find last date using multiple conditions

Formula in cell H3:

=MAX(INDEX((C2:C29=H1)*(D2:D29=H2)*E2:E29,))

Array formula in cell H3:

=MAX(IF((C2:C29=H1)*(D2:D29=H2),E2:E29,""))

How to create an array formula

Back to top

Lookup and find latest date on multiple sheets

The following picture shows you a workbook with 4 worksheets. The formula in cell B3 looks for the latest date in all three worksheets using the condition in cell B2.

Array formula in cell B3:

=MAX(IF(B2=January!$B$2:$B$10, January!$A$2:$A$10, ""), IF(B2=February!$B$2:$B$10, February!$A$2:$A$10, ""), IF(B2=March!$B$2:$B$10, March!$A$2:$A$10, ""))

Download excel *.xlsx file

Find-last-appointment-date.xlsx

Back to top

Lookup and find latest date, return corresponding value on same row

Enter a quarter in cell G3.

Array formula in cell G3:

=MAX((B3:B19=G2)*C3:C19)

If you prefer a regular formula in G3:

=MAX(INDEX((B3:B19=G2)*C3:C19,))

Formula in cell G4:

=INDEX($D$3:$D$19, SUMPRODUCT((B3:B19=G2)*(G3=C3:C19)*MATCH(ROW(B3:B19), ROW(B3:B19))))

Watch a video explaining the formulas above

Back to top

Functions in this article:

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value,lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value

MAX(number1,[number2],..)
Returns the largest value in a set of values. Ignores logical values and text.

Back to top