Table of contents

  1. Lookup a value and find max date
  2. Lookup all values and find max date

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 C5
  2. Paste array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

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)

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

  1. Select cell C2
  2. Paste formula
  3. Press and hold Ctrl + Shift
  4. Press Enter
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

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

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