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
- 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.
Related posts:
Excel: Find latest date in a list
Lookup two index columns using min max values and a date range as criteria
Recommended categories
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.