Find latest date based on a condition
Table of contents
- Lookup a value and find max date
- Lookup a value and find max date (Pivot Table)
- Lookup all values and find max date
- Lookup and find last date using multiple conditions
- Lookup and find latest date on multiple sheets
- Lookup and find latest date, return another value on same row
- Functions in this article
Lookup a value and find max date
The picture below 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.
Update, 2017-08-15! Added a regular formula.
Formula in cell F4:
Array formula in F4:
Formula in cell F4 (Excel 2016):
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 […]
How to create an array formula
- Double click 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:
Array formulas allows you to do advanced calculations not possible with regular formulas.
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 click "Evaluate Formula" button. Click "Evaluate" button, shown on the dialog box, to move to next step.
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 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.
How to set up Pivot Table
- Select the cell range containing the data.
- Go to tab "Insert" on the ribbon.
- Click 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. - Click OK button.
- Click on Values and drag to Filters field, see blue arrow above.
- Click on Dates and drag to Values field.
- Click on "Count of Dates".
- Click on "Value Field Settings...".
- Click on "Max" to select it.
- Click on "Number Format" button.
- Click on category "Date" and select a type.
- Click OK button.
- Click OK button.
Click 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.
Lookup all values and find latest (earliest) date
The following formula looks in column C for the most recent date for each value in column B.
Formula in cell D3:
Array formula in cell D3:
How to create an array formula
Formula in cell D3:
Watch a video explaining the formula above
How to copy array formula
- Copy cell C2
- Select cell range C3:C8
- Paste
Lookup and find last date using multiple conditions
Formula in cell H3:
Array formula in cell H3:
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:
Lookup and find latest date, return corresponding value on same row
Enter a quarter in cell G3.
Array formula in cell G3:
If you prefer a regular formula in G3:
=MAX(INDEX((B3:B19=G2)*C3:C19,))
Formula in cell G4:
Watch a video explaining the formulas above
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
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
MATCH(lookup_value,lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value
MAX(number1,[number2],..)
Returns the largest value in a set of values. Ignores logical values and text.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
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 […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
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 […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
Count dates inside a date range
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)) + […]
How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]
101 Responses to “Find latest date based on a condition”
Leave a Reply to OVM
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.
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:
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:
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))
Download 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/