## Lookup a value and find max date in excel

**Table of contents**

### Lookup a value and find max date

**Lookup a value in A8:A14 and return max date**

Array formula in C5:

**How to create an array formula**

- Doubleclick C5
- Paste array formula
- Press and hold Ctrl + Shift
- 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:**

**How to create an array formula**

- Select cell C2
- Paste formula
- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula**

- Copy cell C2
- Select cell range C3:C8
- 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.

### 67 Responses to “Lookup a value and find max date in excel”

### Leave a Reply

**How to add vba code to your comment:**

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

your code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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,

Array formula in cell B3:

I recommend using tables, one for each sheet. If you add more rows to a table you wouldn´t have to adjust the corresponding cell reference in the array formula.

Download excel *.xlsx file

Find-last-appointment-date.xlsx

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,

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

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

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:

Download 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:

http://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