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 A (A8:A14) and dates in column B (B8:B14). The formula in cell C5 lets you search for value and return the latest date in adjacent column for that value.

Lookup value and return max date

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

Formula in cell C5:

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

Array formula in C5:

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

Recommended article:

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

Formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) How to enter an array formula Select cell E3 Type or copy/paste above […]

Comments(40) 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: Built-in features, 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 max date

The following formula looks in column A for the most recent date for each Value.

Formula in cell C2:

=IF(MAX(INDEX((A2=$A$2:$A$8)*$B$2:$B$8,))=B2,"Newest","")

Array formula in cell C2:

=IF(MAX(IF(A2=$A$2:$A$8, $B$2:$B$8))=B2, "Newest", "")

How to create an array formula

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 another value on same row

Enter a quarter in cell F2

Array formula in cell F3:

=MAX(IF(F2=A2:A9,B2:B9,""))

Array formula in cell F4:

=INDEX($C$2:$C$9, MATCH(1, COUNTIFS(F2, $A$2:$A$9, F3, $B$2:$B$9), 0))

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