Table of contents

  1. Lookup a value and find max date
  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 

Lookup a value and find max date

Lookup value and return max date

Lookup a value in A8:A14 and return max date

Array formula in C5:

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

How to create an array formula

  1. Doubleclick cell
  2. Copy Paste array formula
  3. Press and hold Ctrl + Shift simultaneously
  4. Press Enter
  5. Release all keys

The formula begins and ends with a curly brackets, don't enter these characters yourself.

Explaining array formula in cell C5

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.xls
(Excel 97-2003 Workbook *.xls)

Back to top

Lookup all values and find max date

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-newest.xls

Back to top

Lookup and find last date using multiple conditions

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.