Excel Magic Trick 942: Lookup Last Shipment Date For Customer, Sorted & Not Sorted Date Column

[@hsm22]

Thanks. Great and clearr explanation.

[@excelisfun]

Glad this helps!

[@excelisfun]

That is beautiful, IncongruentBalance!! I guess the reason I always stay away from D Functions is because they are hard to copy down a column because of the criteria argument setup requirement. I love hanging out with our online Excel Team because I would have never "seen": the DMAX solution if it were not for you, IncongruentBalance !!

[@excelisfun]

I am glad that you like it!

[@tiddbits.6693]

You are a genius and a saint.

[@andrewlindenboom5518]

Thank you. This video was very helpful and it was exactly what I was looking for.

[@ridhoakbari9319]

thank you, sir... very usefull for my job...

[@wasimabbas6280]

Really appreciated your work.

[@mahadibrahim2586]

Thank you so much Mike, you really made my life easy. much appreciated.

[@excelisfun]

You are welcome, Mahad! I am glad that the resource I post help make your life easier. Thanks for the support with your comments, Thumbs ups and your Sub : )

[@k0m0sh1n]

Excel GENIUS!! Thank you so much!!!!

[@excelisfun]

You are welcome so much! Thanks for your support : )

[@excelisfun]

Yes, but it is invloved: Alt + Shift + F10 (keyboard to open any "Smart Tag") Then the letter "o" Altogether it is: Alt + Shift + F10, o

[@basudevbasak309]

This is God level @Mike.. Thank you so much

[@excelisfun]

You are welcome!!!

[@excelisfun]

Not really. What formula would you use with DMAX?

[@krn14242]

Cool trick, thanks Mike. Simply amazing

[@NunoCastilho]

Excellent video, thank you.

[@excelisfun]

You are welcome, Nuno!!!! Thanks for your support with your comments, thumbs up and Sub : )

[@nereidenene7731]

God bless you!

[@720jsh]

first i love your videos . do you have an index of tips which includes the tip no and the short description. I'm trying to build a spreadsheet with a lot of different things you can do and that would be helpful .

[@sanjitpatel6491]

Thanks.. it solved my purpose..

[@IncongruentBalance]

Mike was there any particular reason you didn't use DMAX?

[@mariasmith2262]

Thank you! very helpful! =)

[@excelisfun]

Glad this helps!

[@mohdaijaz148]

Great Video, Can you help me with this - Update Product Prices without Losing the Old Price Records, I am using Vlookup to Pull Price, But if I am updating the existing price then it changes all old records. kindly help me with this I am really stuck at this

[@RiyasNechiyan]

Thank you sir 👍👍👍

[@excelisfun]

You are welcome, Riyas!!!!

[@muhammadnauman1454]

What about to get earliest date ? i am having issue due to 0 while using MIN function instead of MAX

[@_chess_master]

Thank you, very helful information. If the list is constantly growing is it possible to have this sybtable dynamic? I have a similar table (list of drawings) and in my case abc would be in three columns (a in one column, b in other and c in yet another). Should & be used to join the columns together when using a formula? And the last question is is it possible to achieve the same result using an advanced filter with formula? Thank you

[@mudassiralirana4256]

What should I have to do if there is any nagetive value in colum D ? This formula does not show nagetive value if it arrives in colum D

[@nitilanchan7787]

one error found in above formula, that in above example, if the same date is mentioned for xyz is 11/5/2012, then the latest date will pick 222 for abc. please advise.

[@swapnilmule7762]

very helpful.

[@excelisfun]

Glad it is helpful! Thanks for your support with your comment, Thumbs Up and your Sub, swapnil mule!!!!

[@pwrsrg7777]

Hi Mike, I had a project where I had to return the earliest date of unsorted data. Would I use the SMALL function - or could I ever use the LOOKUP function in this case?

[@jordanwills182]

You are the man! Made me money! 2

[@excelisfun]

Glad it helps, Jordan! Thanks for the support with your comment, Thumbs Up and Sub : )

[@gregsdoodles4547]

how do you do it with 2 criteria? The AND function doesn't seem to work

[@excelisfun]

In second argument something like: ((Range=Condition)*(Range=Condition)) you can use other comparative operators such as >, =,

[@jurdegreeuw]

At 3:15 you said: "I gonna point to fill without formatting". Is there a keyboard shortcut for "Fill without formatting" after dragging the fillhandle? Thanks and greetings from the netherlands

[@gunindradas8042]

My data is horizontally how I can sir solve it. First row for date 2 nd row is sell data. I need the last date. Same table but horizontally.

[@AniManuSCh]

Nice video, Can some one help me? I have a row with dates and below it I have an empty row where I can tipe a "C" on any number of cells wanted or needed to get the dates above it, but I only get the first C, so what I am trying to do is actually ignore every "C" that I have wrote that correspond to past dates from today and only give me the closer one or next one from today. For example: lets say that today is July 1 2018, so I have row 1 with a series of dates like A1=June 30 2018, B1= July 1 2018, C1=July 2 2018, D1=July 3 2018, E1=July 4 2018, etc and in row2 I have typed C on A2, C2 and E2 so with HLOOKUP it returns June 30 2018, but I dont want that since that date is now in the past, I want to get the next and more close date after the present day so it should be C2 that is July 2 2018, and so on since the today formula will update as the days pass. This is to get the next checking date on a project cronogram so all the dates are defined but the result as for each task should be automatically updating to stay relevant for the scehdule, so past dates just dont make any sence to be the results showed on the gantt table diagram, and yes I know it is easy as just to be deleating the "C" that correspond to past dates, but then what is the point of excel then?

[@IncongruentBalance]

{G1:f2} = {Product,Shippment date;ABC;=DMAX...} G2 still had the data val, so changing the target product is easy enough

[@excelisfun]

Hey, send me link after you post to Mr Excel Message Board, and I will take a look later...

[@OMGIndia-vd9ls]

what if same date have two different value??, i tried it but failed please help me

[@aamir6282]

Dear sir I want find the last entry of multiple times in single day your formula work to find the entry of date it doesn't work for me I want to find last entry of time for example the employee 🆔 1234 who enter in 6 June 15:00,15:30,17:00 and 17:15 I want to find 17:15 for the 🆔 1234

[@carlosguerra5970]

How if rate has 3 lookup columns?

[@IncongruentBalance]

=DMAX($A$1:$D$11,G1,F1:F2) and =SUMPRODUCT($D$2:$D$11,--($B$2:$B$11=F$2),--($C$2:$C$11=G2)) of course the only issue here is that you have to change the format of the sheet a little to use DMAX, but the same formula works for both approaches.

[@mudassiralirana4256]

Plzzz tell me how I can have a nagetive value if it arrives in colum D ? I wants to show just like debit of a customer it will always arrives in nagetive so what should I have to do if I wants to show that plzzz guide ... With bundle of thanks in advance

[@teamrnetinternet-communica485]

can you give me same for minimum

[@excelisfun]

I do not. I am sorry.

[@soubhagyakumar8048]

Unable to find 942 excercise

[@excelisfun]

Here you go: people.highline.edu/mgirvin/KZbinExcelIsFun/EMT942.xlsx

[@yansopheak9265]

I can not do it

[@rainer36]

try to insert the same max date for xyx and it will give you wrong results

[@bootsdeguia2940]

I CANT GET THE 3:12 TO 3:23 PART MY RESULT IS NOT THE SAME

[@excelisfun]

working 70+ hours this week. No time. Try: mrexcel [dot] com/forum