Lookup a value in A8:A14 and return max date

Array formula in C5:

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)

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.