## Find latest date based on a condition

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The condition is specified in cell F2 and the result is in cell F4.

For example, the condition is met in cells B5, B7, and B9. The corresponding dates in column C on the same row are 8/1/2022, 9/6/2022, and 7/29/2022.

The formula returns 9/6/2022 in cell F4 which is the last date of these dates.

**Table of contents**

## 1. Lookup a value and find the last date

The picture above shows you values in column B (B3:B9) and dates in column C (C3:C9). The formula in cell F4 lets you search for value and return the latest date in an adjacent or corresponding column for that value.

#### 1.0.1 Regular formula

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

The following formula is a regular formula, it is somewhat more complicated to understand, however, regular formulas are not prone to errors like array formulas. For example, inexperienced Excel users may easily break array formulas.

Formula in cell F4:

#### 1.0.2 Array formula

The following formula is an array formula, section 1.2 explains how to enter an array formula.

Array formula in F4:

Section 1.3 explains how the formula above works in great detail.

#### 1.0.3 Excel 2016 - regular formula

Formula in cell F4 (Excel 2016):

The MAXIFS function was introduced in Excel 2016, it is entered as a regular formula. You need Excel 2016 or a later Excel version to use this function.

#### 1.0.4 Excel 365 formula

**Update, 2021-09-09! **Added an Excel 365 formula.

Formula in cell F4 (Excel 365):

The FILTER function is a new great function introduced in Excel 365.

### 1.1 Watch a video where I explain the formulas

Recommended article:

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

**1.2 How to create an array formula**

- Double press with left mouse button on cell C5
- Copy / Paste above array formula
- Press and hold Ctrl + Shift simultaneously
- Press Enter
- 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:

A beginners guide to Excel array formulas

Array formulas allows you to do advanced calculations not possible with regular formulas.

A beginners guide to Excel array formulas

### 1.3 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 press with left mouse button on "Evaluate Formula" button. Press with left mouse button on "Evaluate" button, shown on the dialog box, to move to next step.

#### Step 1 - Find values equal to lookup value

The equal sign is a logical operator that lets you compare value to value, in this case, a comparison value to values is performed. The output is an array of boolean values.

C3=A8:A14

becomes

"EE"={"AA"; "CC"; "EE"; "BB"; "EE"; "VV"; "EE"}

and returns

{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}

Boolean values are either True or False, their numerical equivalents are True - 1 , False - 0 (zero).

#### Step 2 - Convert boolean values to corresponding dates

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

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

The MAX function allows you to calculate the largest number in a cell range. It ignores text, boolean and empty values, however, not error values.

MAX(*number1*, [*number2*], ...)

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

becomes

MAX({FALSE; FALSE; 40323; FALSE; 40149; FALSE; 40195})

and returns 40323 formatted as 2010-05-25.

### 1.4 Excel file

## 2. Lookup a value and find max date (Pivot Table)

The formulas demonstrated in this article may be too slow or taking too much memory if you work with huge amounts of data. The Pivot Table is an excellent option in such cases, it is remarkably fast even with lots of data.

### 2.1 How to set up Pivot Table

- Select the cell range containing the data.

- Go to tab "Insert" on the ribbon.
- Press with mouse on "Pivot Table" button.
- A dialog box appears.

I usually place the Pivot Table on a new worksheet so it doesn't hide parts of the data set while filtering etc. - Press with left mouse button on OK button.

- Press with mouse on Values and drag to Filters field, see blue arrow above.
- Press with mouse on Dates and drag to Values field.
- Press with mouse on "Count of Dates".

- Press with mouse on "Value Field Settings...".

- Press with mouse on "Max" to select it.
- Press with mouse on "Number Format" button.

- Press with mouse on category "Date" and select a type.
- Press with left mouse button on OK button.
- Press with left mouse button on OK button.

Press with mouse on cell B1 to filter the latest date based on the selected value, the image above shows value EE selected and the latest date based on that value is 5/25/2010.

### 3. Lookup all values and find the latest date

The following formula looks in column C for the most recent date based on the value in column B on the same row.

For example, cell B3 contains "AA". Cells B10 and B13 also contain "AA". The corresponding cells on the same row are C3, C10, and C13. They contain these dates "December 9, 2009", "March 4, 2010", and "December 5, 2010".

Cell D3 is not populated with the text "Latest", cell C3 is not the latest date. Cell C13 contains the last date based on the value "AA" in column B on the same row.

The following formula is a regular formula if you prefer that.

Formula in cell D3:

The next formula is an array formula, section 1.2 explains how to enter an array formula.

Array formula in cell D3:

The last formula contains an Excel 2016 function, MAXIFS function.

Formula in cell D3:

### 3.1 Watch a video explaining the formula above

**3.2 How to copy array formula**

- Copy cell C2
- Select cell range C3:C8
- Paste

### 3.3 Explaining formula

This section explains the array formula.

#### Step 1 - Identify cells that meet condition

The equal sign is a logical operator that compares value to value, it returns boolean values True or False.

B3=$B$3:$B$14

becomes

"AA"={"AA"; "CC"; "EE"; "BB"; "EE"; "VV"; "EE"; "BB"; "CC"; "VV"; "AA"; "CC"}

and returns

{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.

#### Step 2 - Replace boolean value with corresponding dates

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(B3=$B$3:$B$14, $C$3:$C$14)

becomes

IF({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}, $C$3:$C$14)

becomes

IF({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}, {44554; 44583; 44774; 44725; 44810; 44484; 44771; 44241; 45101; 44218; 44559; 44521})

and returns

{44554; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 44559; FALSE}

#### Step 3 - Extract latest date

The MAX function allows you to calculate the largest number in a cell range. It ignores text, boolean and empty values, however, not error values.

MAX(*number1*, [*number2*], ...)

MAX(IF(B3=$B$3:$B$14, $C$3:$C$14))

becomes

MAX({44554; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 44559; FALSE})

and returns 44559.

#### Step 4 - Compare with corresponding date on the same row

MAX(IF(B3=$B$3:$B$14, $C$3:$C$14))=C3

becomes

44559=44554

and returns False.

#### Step 5 - Return "Latest" if True

IF(MAX(IF(B3=$B$3:$B$14, $C$3:$C$14))=C3, "Latest", "")

becomes

IF(FALSE , "Latest", "")

and returns "" nothing in cell D3.

### 3.4 Excel file

## 4. Lookup and find the last date using multiple conditions

This example demonstrates a formula that uses two conditions to filter the latest date.

For example, the first condition is "Ram" and the second condition is "Pen". The formula calculates the latest date from column E if both conditions are true on the same row.

The first condition and the second condition are both found on rows 2 and 15, they are highlighted in the image above. The corresponding dates are 4/10/2017 and 11/1/2014, the latest date is 4/10/2017 which is returned in cell H3.

Formula in cell H3:

Array formula in cell H3:

How to create an array formula

### 4.1 Explaining array formula

#### Step 1 - First condition

The equal sign is a logical operator that compares value to value, it returns boolean values True or False.

D3:D30=I2

becomes

{"Ram"; "Shyam"; "Ram"; "Rocky"; "John"; "Shyam"; "Rocky"; "Lalit"; "Sita"; "Rocky"; "Alex"; "Peter"; "Alex"; "Ram"; "Ram"; "Shyam"; "Ram"; "Rocky"; "John"; "Shyam"; "Rocky"; "Lalit"; "Sita"; "Rocky"; "Alex"; "Peter"; "Alex"; "Ram"}="Ram"

and returns

{TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}.

#### Step 2 - Second condition

E3:E30=I3

becomes

{"Pen"; "Pen"; 0; "Pen"; 0; 0; 0; "Pen"; "Pen"; 0; "Pen"; 0; 0; "Pen"; 0; 0; 0; "Pen"; "Pen"; 0; 0; 0; "Pen"; 0; 0; "Pen"; 0; 0}="Pen"

and returns

{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}

#### Step 3 - Multiply arrays - AND logic

The asterisk lets you multiply arrays, this performs AND-logic meaning if both values are TRUE then return TRUE or their numerical equivalent 1. All other combinations return FALSE or 0 (zero).

The parenthesis lets you control the order of calculation, we want to compare values before we multiply the arrays.

(D3:D30=I2)*(E3:E30=I3)

becomes

({"Ram"; "Shyam"; "Ram"; "Rocky"; "John"; "Shyam"; "Rocky"; "Lalit"; "Sita"; "Rocky"; "Alex"; "Peter"; "Alex"; "Ram"; "Ram"; "Shyam"; "Ram"; "Rocky"; "John"; "Shyam"; "Rocky"; "Lalit"; "Sita"; "Rocky"; "Alex"; "Peter"; "Alex"; "Ram"}="Ram")*({"Pen"; "Pen"; 0; "Pen"; 0; 0; 0; "Pen"; "Pen"; 0; "Pen"; 0; 0; "Pen"; 0; 0; 0; "Pen"; "Pen"; 0; 0; 0; "Pen"; 0; 0; "Pen"; 0; 0}="Pen")

becomes

{TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}*{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}

and returns

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

#### Step 4 - Replace boolean values with corresponding dates

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF((D3:D30=I2)*(E3:E30=I3),F3:F30,"")

becomes

IF({1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, F3:F30, "")

becomes

IF({1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, {42835; 41567; 41567; 41944; 41944; 42464; 42835; 41598; 41567; 42364; 42835; 41944; 42835; 41944; 42364; 42358; 41628; 41470; 42835; 42541; 41567; 41858; 42835; 41470; 41307; 42464; 42364; 42464}, "")

and returns

{42835; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 41944; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""}.

#### Step 5 - Extract latest date

The MAX function allows you to calculate the largest number in a cell range. It ignores text, boolean and empty values, however, not error values.

MAX(*number1*, [*number2*], ...)

MAX(IF((D3:D30=I2)*(E3:E30=I3),F3:F30,""))

becomes

MAX({42835; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 41944; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""})

and returns 42835 (4/10/2017) which is the largest number in the array.

### 4.2 Excel file

## 5. Lookup and find latest date on multiple sheets

The picture above shows a workbook with 4 worksheets named "Search", January, February, and March. The formula in cell B3 looks for the latest date in all three worksheets using the condition in cell B2.

Unfortunately, it is not possible to use 3d references with IF functions. The formula contains three IF functions to make it possible to search all three worksheets.

Array formula in cell B3:

### 5.1 Explaining formula

#### Step 1 - Logical test

B2=January!$B$2:$B$10

#### Step 2 - Filter dates from worksheet named January

IF(B2=January!$B$2:$B$10, January!$A$2:$A$10, "")

#### Step 3 - Logical test

B2=February!$B$2:$B$10

#### Step 4 - Filter dates from worksheet named February

IF(B2=February!$B$2:$B$10, February!$A$2:$A$10, "")

#### Step 5 - Logical test

B2=March!$B$2:$B$10

#### Step 6 - Filter dates from worksheet named March

IF(B2=March!$B$2:$B$10, March!$A$2:$A$10, "")

#### Step 7 - Calculate largest number from three arrays

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

### 5.2 Excel

## 6. Lookup and find latest date, return corresponding value on same row

Enter a condition in cell G3, the formula extracts the latest date. Another formula in cell G3 gets the corresponding value on the same row from column D.

Array formula in cell G3:

If you prefer a regular formula in G3:

=MAX(INDEX((B3:B19=G2)*C3:C19,))

Formula in cell G4:

### 6.1 Watch a video explaining the formulas above

### 6.2 Explaining formula in cell G4

Formulas in cell G3 are explained in section 1 above.

#### Step 1 - First condition

B3:B19=G2

#### Step 2 - Find latest date

B3:B19=G2

#### Step 3 - Multiply arrays

(B3:B19=G2)*(G3=C3:C19)

#### Step 4 - Create number sequence

ROW(B3:B19)

#### Step 5 - Create number sequence from 1 to n

MATCH(ROW(B3:B19), ROW(B3:B19))

#### Step 6 - Multiply sequence with array from step 3

(B3:B19=G2)*(G3=C3:C19)*MATCH(ROW(B3:B19), ROW(B3:B19))

#### Step 7 - Sum numbers in array

SUMPRODUCT((B3:B19=G2)*(G3=C3:C19)*MATCH(ROW(B3:B19), ROW(B3:B19)))

#### Step 8 - Get value

INDEX($D$3:$D$19, SUMPRODUCT((B3:B19=G2)*(G3=C3:C19)*MATCH(ROW(B3:B19), ROW(B3:B19))))

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

This article explains how to find the smallest and largest value using two conditions. In this case they are date […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]

Danielle asks: I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]

If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]

The formula in cell C3 calculates the last date for the given month and year in cell B3. =DATE(YEAR(B3), MONTH(B3)+1, […]

Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]

Excel contains a great function that easily counts the number of workdays between two dates, it even allows you to […]

The image above demonstrates the DATEDIF function calculating the number of complete years between two dates. Column B and column C […]

The image above shows a formula in cell D3 that extracts the latest date in cell range B3:B15. =MAX(B3:B15) The MAX […]

The DATEDIF function in cell E3 allows you to calculate days between two dates. Related articles Highlight top 10 % […]

The image above shows the DATEDIF function calculating the number of complete months between two dates. Column B and column C […]

The image above demonstrates a formula that calculates the number of complete weeks between two dates. Column B and column C […]

### 103 Responses to “Find latest date based on a condition”

### Leave a Reply to Michael

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

This is a great example. I was attempting to figure out how to get the greatest date based on a log on ID. This example fit the bill perfectly. Many thanks!!

Thanks for your comment!

Excellent example and explanation. This helped me tremendously and saved me a LOT of time. Thank you so much!

Brent,

Thanks for commenting!

Totally agree, I haven't used arrays before and this was a great intro. Thanks!

JS,

thanks! I wrote an explanation.

Great info. My scenario is slightly different. I'd like the result to come up in each row in column C. If the date is MAX for that rows value(column A) then say "Newest" else blank.

ie. C8 would result "Newest" AA is the only instance on the list. C10 would result "Newest" as it has the MAX date for all the EE's. Consequently C12 and C14 would result Blanks as it is not the MAX date.

Any help would be appreciated.

Albert S,

Great question, I added new content to this post: Lookup all values and find max date

Hello,

Just stumbled across your post trying to find a solution and this was just perfect !

Thank you very much.

- Jyri

Jyri,

Thanks for commenting!

Thanks, this is just what I was after!

You are welcome!

Thank you! This is a lovely neat little formula.

Thank you for commenting!

Hi,

I am in desperate need of some help. I have column A with multiple dates against which I have REGISTRATIONS of FLEET CARS. I want to find and match the most recent date or the last date that a REG took a passengers fair. Any help would be much appreciated.

DATE CAB REG

1/26/2012 LT61ZND

1/26/2012 LT61ZND

2/25/2012 BO51CAB

2/25/2012 LM56FCO

2/25/2012 LM56FCO

2/25/2012 LP10FXU

2/25/2012 LP10FXU

2/25/2012 LL07FYV

2/26/2012 LS60YLG

2/26/2012 LM06WKD

2/26/2012 LG02UPP

2/26/2012 LT61ZND

2/26/2012 W198WGH

2/26/2012 LS51BKG

2/26/2012 LF53EVC

2/26/2012 LS51BKG

HI,

I forgot to mention that the Registrations of fleet cars are in column B.

many thanks,

Natway :-)

Natway,

I this what you are looking for?

This has been very helpful, however I need to be able to look through several sheets for the most recent date. Is that possible? I have my workbook set up with each month on a separate sheet. I'm trying to find the last appointment date for each of my clients.

erica,

Is that possible?I am not sure, you would have to change the formula every time you add a sheet, unless you are allowed to use vba?

I wouldn't be adding any more sheets. I'm not sure how to wright the formula to look through all the sheets. I'm a bit of a novice at excel. I'm not sure what vba is either.

erica,

https://www.get-digital-help.com/2009/12/05/lookup-a-value-and-find-max-date-in-excel/#multiplesheets

Eureka! Thank you, thank you. I knew there had to be a way.

Hello, this is great but i need to get the most recent value in a list of dates. For example, in the ist below I need to be able to get the most recent value (either 1,2,or 3) posted for the quarter.

Quarter Date Value

4 12/5/2012 1

4 12/5/2012 3

1 1/3/2012 2

1 2/15/2012 3

2 4/13/2012 1

2 3/12/2012 3

3 7/25/2012 1

3 9/2/2012 2

Here is the formula I am currently using which is averaging the numbers, dont need to do that anymore. I only need to get the most recent value for a given quarter.

=SUM(IF(COUNTIF(Q127:Q130,1)=0,1,AVERAGEIF(Q127:Q530,1,S127:S530)))

Eugenious1,

https://www.get-digital-help.com/2009/12/05/lookup-a-value-and-find-max-date-in-excel/#return

Hello oscar, i have tried your second example, but everytime i try it i only get a newest entered where the cells is equal to EE, all other values show a blank, unlike yours where each unique value of the A column shows that to be the newest entry. Why is that?

Subash,

Did you enter the formula as an array formula?

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

The formula returns "Newest" if it is the latest record.

Example,

EE has three entries and only the latest date returns "Newest". The other values are unique and returns "Newest".

Below is the formula I am using. Can you tell me what I did wrong?

Column O is the value I want to look up (103046)

Column C is the range I am looking O up in

Column E is the result I want (which should be the most recent date) (answer is 8/20/12)

The answer for this should give me 8/20/12 and I keep getting 12/13/12. I have formatted both date columns the same.

=MAX(IF(O3=C3:C50,E3:E50,""))

So I believe it is giving me the max date in column E not the maximum date in E that relates to the value I am looking up. Any help with a formula?

Column C Column E

103046 8/20/12

103046 6/10/11

103046 1/10/11

108003 12/13/12

108139 12/12/12

122007 5/06/11

122007 8/1/12

Angie,

Your array formula is fine, I don´t think you have entered the formula as an array formula.

See array formula instructions above in this post.

Hello Oscar,

Thanks again, i did realize that mistake i was making and got it corrected, it worked perfectly. :-)

Hi Oscar,

I am trying your formula

=MAX(IF(D38=B4:B32,J4:J32))

but I am getting #value error

Also I use

=MAX(IF(D39='Sheet1'!$B$4:$B$32,'Sheet1'!$J$4:$J$32))

Then also I am getting same error

Nevermind its working now !!

when I followed these steps

3.Press and hold Ctrl + Shift

4.Press Enter

Now can you tell me why these steps are necessary and why it does not work without it ?

Hi Oscar, i am regularly following your site, really helped.

Name Target Announced Date Beneficiary Proceeds

Kiran Microsoft 2/13/2013 500

Kiran Microsoft 2/14/2013 200

Kiran Google 2/12/2013 500

Sriram Microsoft 2/12/2013 500

Sitaram Microsoft 2/12/2013 500

Based on Name, Target, latest date i need beneficiary Proceeds in a column.

I am trying in this way: 1) identifying latest date =MAX(IF($L$2:$L$50=L2,$M$2:$M$50))--But not getting for two column critria

2) Indexing based in latest date: =INDEX($S$2:$S$13,MATCH(1,($L$2:$L$13=L2)*($M$2:$M$13=X2)*($K$2:$K$13=K2),0))

2nd one is perfect and first one i am unable to get value based on two columns criteria.

Is there any alternate way to display Value based on multiple criteria (atleast 2) with latest entry.

Kiran,

Array formula in cell A12:

=MAX((A2:A6=A9)*(B9=B2:B6)*C2:C6)

Array formula in cell B12:

=INDEX($D$2:$D$6,MATCH(1,($A$2:$A$6=A9)*(B9=$B$2:$B$6)*(A12=C2:C6),0))

Dear oscar,

I need newest receiving date of any article when i have more than 500 articles.

Please suggest for it.

For exp

A 12-03-21

B 12-04-13

A 15-03-25

I have used above formula which was

=if(max(if a=a1:a3 etc

But it showing the last date in from the date column.

Pls help

Life saver just changed range to $A$2:$A$999 same for other.

Thanks

Zuber,

I am happy you find it useful.

I am looking for the formula where i can find maximum from column h based on values found matching in column c and column d.

I tried formula given by oscar but it didnot work and give error.

Please suggest

PARDEEP,

Did you enter the formula as an array formula?

Hi Oscar, i am regularly following your site.

how i pick the 37 & 34 & 18 & 15 ID series data with name and latest date visit other duplicates should be deleted.

ID Name ADD1 ADD2 ADD3 interview date

3706 jagdambaenim sdfsdfsd sdfsdf 7/16/2010

3405 ravi kaun sdfsdfsd sdfsdf 7/19/2012

1804 chandan jay sdfsdfsd sdfsdf 7/15/2011

1504 vikash sdf sdfsdfsd sdfsdf 1/18/2010

3706 jagdambajay sdfsdfsd sdfsdf 7/15/2010

3405 ravi enim sdfsdfsd sdfsdf 7/25/2012

1804 chandan sdf sdfsdfsd sdfsdf 7/15/2011

1504 vikash jay sdfsdfsd sdfsdf 1/5/2013

3706 jagdamba nima sdfsdfsd sdfsdf 7/19/2010

3405 ravi dfsdf sdfsdfsd sdfsdf 7/19/2012

1804 chandan nima sdfsdfsd sdfsdf 7/29/2011

1504 vikash dfsdf sdfsdfsd sdfsdf 7/15/2013

3706 jagdamba nima sdfsdfsd sdfsdf 7/13/2010

3405 ravi dfsdf sdfsdfsd sdfsdf 7/19/2012

Deepak,

I can´t come up with a solution for searching multiple text strings.

This one let´s you search for a single text string:

Array formula in cell A20:

Get the Excel *.xlsx file

Search-for-a-text-string-and-find-latest-records.xlsx

Oscar hi ,

I have tried all but I got #value error...why?

Sorry works with ctrl+shift+enter

but what is array formula ? Why do we need that ?

For along time excel user that is the first time I see

Thanks & Regards

jeam,

but what is array formula ? Why do we need that ?You can do more complicated calculations. Try to lookup a value and find max date using regular formulas in one cell only.

How about finding a oldest date if the value is greater than 500for example.(see below)

Column A Column B

22-2-12 200

21-2-12 501

30-3-12 502

20-2-12 503

in this example, there is 3 items which is greater than 500 ( 501, 502, 503) with different date. And the oldest date seems to be 20-2-12. how can i show the oldest date using formula?

Please help, thank you

Louie,

Array formula in cell E2:

=MIN(IF(B1:B4>E1,A1:A4,""))

Thank you for commenting!

Thank you for your excellent explanation!

Just had a slight moment of doubt, all the formula returned were zeros, but it was due to format of the values, I had them as Text.

As soon as I modified them to date it worked perfectly!

Hi Oscar, i am regularly following your site.

how i pick the earliest date in the below given dates in 'A' for value more than 0 in 'B':

Date [A] Value[B]

03-11-2014 702,000.00

23-09-2014 0.00

07-10-2014 283,000.00

02-09-2014 0.00

08-12-2014 346,752.00

28-10-2014 347,375.00

05-11-2014 288,960.00

18-11-2014 290,298.18

17-09-2014 0.00

22-09-2014 0.00

15-09-2014 0.00

25-11-2014 286,383.00

22-12-2014 252,000.00

Hi

I have a problem. I need a formula that returns me the Code associated to the most recent date that has occurred for a particular ID number (the sheet is very long and will have the same ID multiple times. If I could get a formula to return the code for the most recent occurrence that would be amazing!

ID Date Code

1234 12/12/2013 F

2345 15/09/2013 R

3456 21/08/2014 R

1234 01/01/2014 P

I would want the code to bring back the code P.

I am using this Array formula at the moment but it does return the correct code for all, sometimes it picks up other codes.

=INDEX($C:$C,MATCH(MAX(IF(A2=$A:$A,$B:$B)),$B:$B,0))

Unsure, please help.

Hello Oscar,

thank you for sharing you knowledge and helping us with these excellent formulas.

I have a case i could really need your help with:

The following Table shows a history of names, stati and the date the satus was acquired. BUT: people can acquire the same status more than just once (or at least report it). Now I want to know, when each person (peter, sarah & luke) have acquired their individual highest status.

Name Date Status

peter 30.01.2015 5

sarah 30.01.2015 5

peter 28.01.2015 5

sarah 28.01.2015 4

peter 24.01.2015 5

peter 22.01.2015 5

sarah 22.01.2015 3

luke 22.01.2015 4

peter 20.01.2015 3

sarah 20.01.2015 3

sarah 18.01.2015 2

peter 18.01.2015 2

luke 18.01.2015 3

luke 16.01.2015 3

luke 14.01.2015 2

peter 14.01.2015 2

peter 12.01.2015 1

sarah 12.01.2015 2

peter 10.01.2015 1

sarah 10.01.2015 2

sarah 08.01.2015 1

answers have to be :

Names latest status in status since

Peter 5 22.01.2015

sarah 5 30.01.2015

luke 4 22.01.2015

I would really appreciate your help.

Greetings.

Denis,

I made a post for you:

https://www.get-digital-help.com/2015/10/09/find-the-highest-status-and-when-it-was-acquired/

[…] Denis asks: […]

Dear Oscar

I want to search Max date on Monthly and yearly basis

I used MAX with array but failed to get the desired results

Date Result I want Result I am getting

1-Jan-14 12-Dec-15

2-Jan-14 2-Jan-14 12-Dec-15

15-May-15 15-May-15 12-Dec-15

12-Dec-15 12-Dec-15

12-Dec-15 12-Dec-15 12-Dec-15

Regards,

Dear Oscar

I need to return the earliest date for each ID number and would really appreciate your help as if I use any of the above calculations I can't get it to work.

ID No Date

2816746 16/06/2015

2816746 25/06/2015

2816746 16/07/2015

2816746 22/07/2015

2816746 28/07/2015

5269339 11/08/2015

5269339 14/08/2015

5269339 30/09/2015

7088617 22/06/2015

7088617 21/07/2015

7451444 15/06/2015

7451444 30/07/2015

7608629 15/07/2015

7608629 10/08/2015

7608629 11/09/2015

7608629 06/10/2015

8183184 06/08/2015

8261932 14/10/2015

Kind regards

We are a bunch of volunteers and starting a new scheme in our community.

Your site provided us with helpful info to work on. You have performed

an impressive task and our whole group might be grateful to you.

This is what i've been trying to figure out for a while. eloquent statement, detailed explanation...you've really opened my eyes to the power of arrays!

do you have a paypal that i can leave a MODEST donation?

Excellent :)

=MAX(IF((C3=A8:A14;D3=C8:C14);B8:B14;0))

no work in code with muli cons.

=MAX(IF((C3=A8:A14;D3=C8:C14);B8:B14;0))

Lookup value and return max date

Lookup value: EE ok

Search result: #VALUE!

Values Dates

AA 05/12/2009 yes

CC 09/12/2009 no

EE 25/05/2010 no

BB 12/09/2009 no

EE 09/03/2016 yes

VV 14/05/2010 no

EE 17/01/2010 no

=MAX(IF(AND(C3=A8:A14;D3=C8:C14);B8:B14;0))

Hi Oscar, Thanks for your useful post. I have a question...

MY DATE: 2016/02/03

A B C= quantity

1 2016/02/01 BAG 10

2 2016/02/02 BAG 20

3 2016/02/02 Tie 30

4 2016/02/03 BAG 70

5 2016/02/03 BAG 65

6 2016/02/03 BAG 50

7 2016/02/04 BAG 60

I want to lookup LAST quantity from Bag goods at 2016/02/03, in the other hand cell C6=50

Thanks in advance your Reply

Alex,

Array formula in cell B10:

=INDEX($C$1:$C$7, MAX(IF(A10=$A$1:$A$7, MATCH(ROW($A$1:$A$7), ROW($A$1:$A$7)), "")))

This is incredible info. Thank you!

Hi I try this formula. But it is not work for me. It shows True rather than {True,False,False,True} format.

thanks a lot! I am actually learning a lot in this website! Now my Excel skills are surely definitely improved ;) this website is so good!

For example: I say, 01.03.2016 - 31.05.2016 between the dates the book 5 usd. 01.06.2016-31.07.2016 between the dates the book 10 usd.How do you write a formula that he wrote any date range (05.04 2016 or 08.06.2016), and wrote the book price will come automatically.

Hi Oscar,

Please help me.

I need to get the most latest expiration date of my projects but i dont know how to do it.

https://postimg.org/image/gvxln5ww3/

Assuming the columns are a through e. Also assume that the first line of data is on row 5:

=max(if(d5=a5:a20,b5:b20))

Press ctrl shifty enter

Drag down as needed

*consider using data as tables...it makes updates seamless...because you'd be referencing a column not a range that had to be updated

Hi OSCAR ,

I have a problem , please help me

Example Question ,

ID Name VisitDate

1 I-00001 22/2/2016

1 I-00001 30/3/2016

1 I-00001 1/4/2016

2 I-00003 1/5/2016

2 I-00003 1/6/2016

How to write formula I need First Visit Date ???? .Sample ..

ID Name VisitDate FirstVisitDate

1 I-00001 22/2/2016 First

1 I-00001 30/3/2016 Second

1 I-00001 1/4/2016 Third

2 I-00003 1/5/2016 First

2 I-00003 1/6/2016 Second

I need to FirstVisitDate for formula ..Please help me OSCAR and others ....

Thanks and Regards

Yan Aung

Yan Naing

You don't tell me if you want to use a formula or a feature included in excel?

This post describes how to sort a table using an array formula:

https://www.get-digital-help.com/2013/02/11/sort-a-table-with-an-array-formula/

This web page demonstrates excels all built-in sorting capabilities:

https://support.office.com/en-us/article/Sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654

how i can get first calling status and last calling status from below mention table.

Name Date Mobile number Status Last Status First Status

Clark 19-06-2017 10:28 5214521520 Not Decided Call Later New

Clark 19-06-2010 10:28 5214521520 Not Interested

Clark 19-06-2011 10:28 5214521520 Not Decided

Alen 11-03-2016 10:20 9987848254 New Call Later ADC

Alen 11-03-2012 11:50 9987848254 ADC

Alen 11-03-2012 23:50 9987848254 Not Interested

Alen 11-03-2016 23:50 9987848254 Call Later

Ashish

Do you want to search by name and get first and last calling status?

Array formula in cell A12:

=INDEX($A$2:$F$8, MATCH(SMALL(IF(($A$2:$A$8=$B$10), $B$2:$B$8, ""), 1), IF(($A$2:$A$8=$B$10), $B$2:$B$8, ""), 0), COLUMN(A1))

Copy cell (not formula) and paste to cell range B12:F12

Array formula in cell A14:

=INDEX($A$2:$F$8, MATCH(LARGE(IF(($A$2:$A$8=$B$10), $B$2:$B$8, ""), 1), IF(($A$2:$A$8=$B$10), $B$2:$B$8, ""), 0), COLUMN(A1))

Copy cell (not formula) and paste to cell range B14:F14

Hi Oscar,

I am searching from contact number, I have a unique contact number in sheet1, and searching first and last call date and disposition from sheet2.

I have use below formula for first and last calling date but it's not working in larger data

{=MAX(IF(C4= SHEET2!$A$1:$A6$,SHEET2!$B$1:$B$6,MAX(IF(SHEET1!C4=SHEET3!$A$1:$A$5,SHEET3!$B$1:$B$5,""))))}

NOTE : Data size up to 5 lakh and his formula Woking only small data.

Hi Oscar,

My data size is too large. I can use array formula.pls let me know

Hi,

i have data where Consumer Orders are present with their delivery dates. there is a condition that a consumer could have purchased multiple devices so there may be duplicate consumers. i require the last date when a particular device was delivered to a consumer.

Consumer ID Consumer Name Device Delivery Date

1000 1000 Ram Pen 10-04-2017

2000 2000 Shyam Pen 20-10-2013

1000 1000 Ram Pencil 20-10-2013

4000 4000 Rocky Pen 01-11-2014

6000 6000 John Rubber 01-11-2014

2000 2000 Shyam Pencil 04-04-2016

4000 4000 Rocky Pencil 10-04-2017

9000 9000 Lalit Pen 20-11-2013

3000 3000 Sita Pen 20-10-2013

4000 4000 Rocky Rubber 26-12-2015

8000 8000 Alex Pen 01-04-2017

7000 7000 Peter Pencil 26-12-2015

8000 8000 Alex Pencil 10-04-2017

1000 1000 Ram Pen 01-11-2014

1000 1000 Ram Pencil 26-12-2015

2000 2000 Shyam Rubber 20-12-2015

1000 1000 Ram Pencil 20-10-2013

4000 4000 Rocky Pen 15-07-2013

6000 6000 John Pen 10-04-2017

2000 2000 Shyam Pencil 20-06-2016

4000 4000 Rocky Pencil 20-10-2013

9000 9000 Lalit Rubber 07-08-2014

3000 3000 Sita Pen 10-04-2017

4000 4000 Rocky Rubber 15-07-2013

8000 8000 Alex Rubber 02-02-2013

7000 7000 Peter Rubber 04-04-2016

8000 8000 Alex Pen 26-12-2015

1000 1000 Ram Pencil 04-04-2016

Priyanka S

Read Lookup and find last date using multiple conditions

Hi Oscar

Very nice example, thank a lot for your help

I have used this formula for an excel file contain more than 35000 rows and working but the problem that file become very slow , almost impossible

Is it possible to add this array formula in VBA code

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

Thanks in advance for your help

Array formulas are resource monsters. I have used them with hundreds rows and it still takes a while.

Good day Oscar,

Please help me for this.

Item Trasmittal No Date Location

1 08848 12/3/2016 A

2 08850 16/3/2016 D

3 08852 25/3/2016 C

4 08960 4/4/2017 A

5 08965 11/4/2017 C

Let say if I need to look for the final location of the item according to the date, what formula I can use? (In this case, the final location is C).

Thanks for your help.

Hi Oscar,

I have 5 lakh data and I am using this formula but it's not working for all

Hi,

I need the formula to find out the most recent date for the same data. Example:

Date 1Jan. 2Jan. 3Jan. 4Jan. 5.Jan

Rate 0.57 0.28 0.36 0.57 0.19

Please provide me the formula which gives me the result as Rate 0.57 and date 4Jan.

Saleem Ahmed

Is not this what you are looking for?

https://www.get-digital-help.com/2009/12/05/lookup-a-value-and-find-max-date-in-excel/#all

Hi Oscar, thank you so much for this blog. Lots of great valuable information you've provided!

For your first example on this page:

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

How would I combine an error formula with the array formula? I want the result to be blank if no such result is found.

Thank you again

this is simply great. Amazing. Saved a lot of time.

thank you!

Trista,

thank you!

Hello Oscar,

I am attempting to make a formula that will show me the date that an Employee's next point will fall off, and when his last point will fall off. I have looked up several formulas, most with no success and a few that worked but only partially. Do you have any suggestions?

https://postimg.org/image/uu4r9vqqx/

Selina

What determines when a point falls of and when his last point will fall off?

Hello

I tried this myself but it eats a lot of memory when there are large number of rows. I also put the formula in VBA but it caused a "not enough memory" error. I think array functions are great but not suitable for larger datasets. Any idea how to loop this in VBA without using array functions?

Stefan,

great question!

I have added a section for your particular issue in the article:

https://www.get-digital-help.com/2009/12/05/lookup-a-value-and-find-max-date-in-excel/#pivot

Hi Oscar, first of all thank you for taking your time on helping us !

I need your help, if you could please:

Line 1: column b (jan), c (feb), d(mar), e(apr), f(may) ....

Line 2: column b (qty for jan = 10), c (qty for feb= 8), d (qty for mar = 3), e(qty for april = 0), f(qty for may = 0)...

I'm looking for a formula that returns the month in which at least 1 unit was sold: in the example above, the formula would return with the "value": Mar, as the item sold 0 in april and 0 in may...

Would you be able to help me, please?

eD

I believe this article explains how to extract values based on a condition.

Hello sir, thank you for helping us.

I need your help, if you could please:

https://postimg.cc/w1p3m4qg

Lucky

Array formula in cell D2:

=INDEX($H$2:$H$21, MATCH(MAX(IF((A2=$F$2:$F$21)*(B2=$G$2:$G$21)*(C2>$H$2:$H$21), $H$2:$H$21, "")), IF((A2=$F$2:$F$21)*(B2=$G$2:$G$21)*(C2>$H$2:$H$21), $H$2:$H$21, ""), 0))

Get the Excel file

Lucky.xlsx

Hi Oscar,

I have read through all the comments to see if my problem was already answered but I don't believe it has. Here goes:

I have a list of ID numbers and a list of effective dates (with reason codes). I see your examples show how to pull back the most recent date or value for one particular ID but is there a way to do this for each individual ID number? What I mean is I'm trying to be able to basically create a list of ID numbers, with no duplicates, bringing back the most recent effective date, all listed in one place without having to change the ID number each time to find the information needed. I see the examples show how to pick an ID and pull back a date but my data is so massive that there's no way I can manually enter for each ID to find the date.

Does that make sense?

'Lookup and find latest date, return corresponding value on same row'

=INDEX($J$6:$J$66, SUMPRODUCT((B6:B66=M41)*(M36=A6:A66)*MATCH(ROW(B6:B66), ROW(B6:B66))))

I am trying to use the above formula in a simple 'Income-Expense worksheet' to find the values at the end of a given month.The 'B' column equates to the 'ID' column but only has 2 'ID's'-"Cash & Bank", the 'A' column is my Date column. It works until it comes across 2 identical dates & 2 Identical ID's:-

A B

1. 31/01/20 Bank(or Cash)

2. 31/01/20 Bank(or Cash)

The formula then returns '0.00 €'or sometimes '#REF'. What I need the formula to do is to find the last entry-Line 2-when it finds this situation. Can you help please?

Kind regards Mick Foulstone

Array formula in cell C9:

=INDEX($D$3:$D$6, MAX(IF($C$3:$C$6=MAXIFS($C$3:$C$6, $B$3:$B$6, C8), MATCH(ROW(D3:D6), ROW(D3:D6)), "")))

Use this regular formula if you are an Excel 365 subscriber:

=INDEX($D$3:$D$6, MAX(IF($C$3:$C$6=MAXIFS($C$3:$C$6, $B$3:$B$6, C8), SEQUENCE(ROWS(D3:D6)), "")))

or

=XLOOKUP(C8,IF(C3:C6=MAXIFS($C$3:$C$6,$B$3:$B$6,C8),B3:B6),D3:D6,,,-1)

Hi Oscar, thank you for your reply, when I saw the picture you had emailed me-https://www.get-digital-help.com/2009/12/MIC........NE.png">- I thought that is just what I am looking for, so I copied the formula that you sent - 'Array formula in cell C9:

=INDEX($D$3:$D$6, MAX(IF($C$3:$C$6=MAXIFS($C$3:$C$6, $B$3:$B$6, C8), MATCH(ROW(D3:D6), ROW(D3:D6)), "")))'- pasted it to the relevant worksheet, changed the cell references to match my worksheet columns, but it came up with the '#NAME' error. When I evaluated the formula, it underlined MAXIFS as the cause of the #NAME error. After doing some research I found that my version of Excel(2010) doesn't support the MAXIFS function. Unfortunately my knowledge of FUNCTIONS is not good enough to convert the MAXIFS to a MAX(IF....! If you can help I would greatly appreciate it. Many thanks for the other links you sent me.

Kind regards Mick

Mick,

try this array formula:

=INDEX($D$3:$D$6,MAX(IF($C$3:$C$6=MAX(IF($B$3:$B$6=C8, $C$3:$C$6,"")),MATCH(ROW(D3:D6),ROW(D3:D6)),"")))

It should work with Excel 2010.

Oscar, thank you so much for your help & patience with my query. the last correction formula - =INDEX($D$3:$D$15,MAX(IF(($C$3:$C$15=MAX(IF($B$3:$B$15=F9, $C$3:$C$15,"")))*(F9=$B$3:$B$15),MATCH(ROW(D3:D15),ROW(D3:D15)),""))) - works! I did remember to press 'CTRL_SHIFT_ENTER' to make it an Array formula. Once again many thanks to you.

Kind regards.......Mick

Excellent post Oscar and your replies to comments are great. I'm hoping you'll be able to help with my question:

I have a list of clientIDs in column A. In column B I have a list of dates they have been contacted. Each client can be contacted multiple times. Using the formulas above, I can identify the first and last dates of contact, but I'd really like to be able to put a contact number next to each client. For example:

ClientID (A) Date of Contact (B) Contact Number (C)

Mr Apple 01/04/2020 1

Mrs Banana 01/04/2020 1

Mr Apple 07/04/2020 2

Mr Apple 10/04/2020 3

Mrs Banana 12/04/2020 2

Thank you in advance for any help you can offer :)

Donna,

I believe the last section in this article answers your question:

https://www.get-digital-help.com/create-number-sequences-in-excel-2007/

Thanks Oscar. Still helping folks a decade later!

how to pick the previous date or value if the criteria date or value is not available in range.