Excel Magic Trick 1486: VLOOKUP, MAXIFS, & Key: Lookup Correct Price Based on Effective Date
HTML-код
- Опубликовано: 24 апр 2018
- Download Excel File: excelisfun.net/files/EMT1483-...
Entire page with all Excel Files for All Videos: people.highline.edu/mgirvin/ex...
In this video learn how to Lookup the correct product price based on the latest Effective Date. The Lookup table has multiple listings of the product, each with a different effective date and price. In this video learn about how to use the VLOOKUP and MAXIFS functions (also AGGREGATE Function) and a Helper Column (Key Column) in the Lookup Table.
Related Videos:
Excel Magic Trick 1483: LOOKUP Array Formula: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1484: LOOKUP & Sorted Helper Column: Lookup Price Based on Effective Date
Excel Magic Trick 1485: SUMIFS & MAXIFS Functions: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1486: VLOOKUP, MAXIFS, & Key: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1487: INDEX, MATCH & MAXIFS : Lookup Correct Price Based on Effective Date
Excel Magic Trick 1488: Vote For Favorite Formula to Lookup Correct Price Based on Effective Date
I have followed your every video from the very first one including conferences. I can assure you that you have made me become a pro in Excel and out of your lessons, I just recently landed a very good job in a multinational company of which my Excel skills made me stand out as the best candidate from the rest of the over 7,000 applicants. Once again thank you and be blessed.
You are welcome! Congratulations of getting 1 of 7000 with your fun and efficient Excel skills!!! Just as I have helped you over the years, please help me with a Thumbs Up on every video that you watch and a comment when you are inspired.
That's a smart use of MAXIFS function inside VLOOKUP. Thank you Mike!
Smart and fun : ) Thanks, Teammate!
Awesome I really feel if I lose your videos i will lose so many things. thank you Mr. Mike
You are welcome, Ismail!!!!
Very cool function combinations Mike! Thanks for the video!
You are welcome as always, Teammate!
Thanks for making Excel fun and for being awesome!
You are welcome, Teammate!!!! Great to be on the Team : )
I respect you from the bottom of my heart. I earnestly request you to come in India if it is possible for you. I can't go to America because of my financial reason. I want to see you. You're my Guru. I've learnt everything from you. May God bless you everything you desire for.
I am glad that the videos help you!
Mike, another great video!
Glad you like it! Thanks for the support, as always Chris!!!!
Thanks Mike, This is EXCELlent
You are welcome, Syed!!!
Extremly useful usage of MAXIFS! Thank you :)
Of course: I liked the video, and of course: thumbs up!
Thank you so much, Teammate Malina!!!!
I can not leave this video without a big thumbs up "with mr excel any way leads to a lot of fun "
Thank you very much for your support : )
making history once again Guru! thanks for such a great formula!
You are welcome for the video, Saul!!! Thanks for your support : )
Wonderful. Thank you for the great insight.
You are welcome, Vincent! Thanks for the support : )
Thanks Mike :) Amazing how one can go about it!!!
It is amazing hoe many ways in Excel there are to have fun : ) You are welcome as always, John!
Perfect !
Appreciated
Glad it helps!!! Thank you for your support with your comment, Thumbs Up and Sub : )
ExcelIsFun Thanks Mike for your great videos.
In old versions, this one also seems to work =LOOKUP(1,1/FREQUENCY(0,1/(($C$11:$C$17
Thanks great video sir
You are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )
superb....
Glad you like it! Thanks for your support : )
very nice deer sir
Glad you like it, aamir!!!! Thank you for the support : )
Thank you, Mike -- another great video. Appreciate your expertise, time and effort.
Interesting to see logic to relate scattered data (in Access, one does not generally scatter stuff all over the place with no defined structure as you can do in Excel (smile) -- but that is also something that makes Excel so flexible and such a great place), and thx for hovering so long on the popup tips ... AGGREGATE seems nice to explore. Many are on older versions so, as a developer, I must consider backward compatibility.
imo, popup tips (or whatever they are being called now) might be better stated as: AGGREGATE( function_num, options, array [, k]) and AGGREGATE( function_num, options, ref1, ref2 [, ref3 [, ref4 [,ref5 ... ] ] ] ) ... any thoughts about that? (placement of [brackets] for optional information is different). Well, this probably applies to other help examples too -- just that this one caught my attention. I looked for a time-code in the video description, but ... maybe you need a database to help you manage those ;) -- they were really nice. Doubt many commented on the extra effort you went through to do them though.
Normally, I would not have to know about AGGREGATE or MAXIFS because Access has other ways ... but so many databases start in Excel -- and the data-lookers generally want it to go back there too ;) ... nice that Microsoft Office products work so seamlessly together ~ ... btw, imo, Access is not necessarily a place to store data -- although it can do that too; it is really more a tool for maintaining, manipulating, and reporting structured information ... and maybe the reporting is an Excel workbook ~
Yes, the good and bad of Excel not structured data, except Power Query...
Yes, ref in AGGREGATE would be better.
I agree with you about what you said: "Access is not necessarily a place to store data -- although it can do that too; it is really more a tool for maintaining, manipulating, and reporting structured information".
BTW, I have an epic AGGREGATE Video Playlist that shows all the different functions and options and other elements here: ruclips.net/p/PL95781BFAAB4162C0
Thanks, Crystal for all of your awesomeness : )
thank you so much, Mike ... Access (or another database) IS a GREAT place to store data ~ then it can be shuffled to Excel and wherever else it needs to go for further manipulation :)
thx
You are welcome!
Dear Mike, Would you add subjects about like production planning, inventory, project management in excel? Thanks!
Not at the current time as AI have a long back log of videos for bnew and up and coming classes. But I will consider for future
First Comment for an awesome video.
First Place - I wish I had a trophy to give you : ) Thank you for the support, Ravi!!
The knowledge you give us is priceless. Waiting for your more amazing videos.
It is not priceless in this way: for each vid you click the Thumbs Up and leave a comment if you are inspired to do so : )
I have a problem and I'm sure it's a cakewalk for you....I have a dataset that I import every month that shows all my employee punch ins and outs. It has everything I need, but the date column imports the date and time in the same cell and I don't need the time at all for the pivot chart I create from the dataset. Is there a way to create a new column next to the date/time cell, then enter a formula in the new cell that references the date/time cell but only enters the date?
If so, can I convert that new column to only contain the new value, then delete the date/time column?
If you are using Power Query to import, then you can use the built-in Power Query Convert to Date features (there are a few ways to do it), or in Excel you can have a formula like; =INT(Date) to get just the date from a date-time.
i am looking for it long long time
Glad you found it! Thanks for your support with your comment, Thumbs Up and Sub : )
Hi,
Can you help me please with few formulas for excel orders?
I have a list of products on rows, and for column I have dates until october. inside I put values from orders, and I want those values to be shown only if they are bigger than 0, or if cells are not blank in another sheet along with values asociated with those products.
If you can, please pm me.
I am not sure, but no problem, try this great Excel Question site for back and forth dialog to get Excel solutions: mrexcel.com/forum
very nice deer sir
You got it, aamir!!!!
yes sir i got it because you explain very nice and your explaination is always help me