### Lookup a value and find 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.

Lookup value and return max date.xls
(Excel 97-2003 Workbook *.xls)

### Lookup all values and find max date

Array formula in cell C2:

How to create an array formula

How to copy array formula

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

### 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

### 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, ""))

Find-last-appointment-date.xlsx

### 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))