Advanced Lookup Formula to Show 3 Lowest Prices with Supplier Name, Including Ties. EMT 1737.
HTML-код
- Опубликовано: 6 июл 2024
- Download Excel File: excelisfun.net/files/EMT1737....
Learn how to lookup and display the three lowest transportation prices and transporter name from a large lookup table with From Location, To Location and Size of Truck. There-way lookup to return multiple records. See the functions: XLOOKUP, FILTER, XMATCH, LET, SORT, COUNTIFS, SMALL and ISNUMBER.
Learn how to:
1. (00:00) Explanation of the there-way lookup to return multiple records
2. (00:35) Intro Song
3. (00:42) Older Methods if you do NOT have Microsoft 365
4. (01:08) Lookup price column with XLOOKUP
5. (01:51) FILTER to filter prices to get the third smallest value.
6. (02:26) Look at semi-colons in array that instruct FILTER Dynamic Array Function to filter by rows.
7. (02:46) SMALL function to get third smallest price.
8. (03:09) FILTER function to filter by rows.
9. (03:40) Discussion of AND Logical tests in include argument of FILTER, including why the formula returns 0 and 1 values.
10. (04:45) Vertical Array that allows FILTER to filter by rows.
11. (05:29) FILTER function to filter columns. Use XMATH and ISNUMBER functions.
12. (06:13) Look at columns in resultant array that instruct FILTER Dynamic Array Function to filter by columns.
13. (07:08) Bonus #1. COUNTIFS and IF function to deal with situations where there are fewer records than three. Use these in k argument of SMALL.
14. 07:53) Bonus #2: LET function to define variable when there are duplicate formula elements. Leads to a more efficient calculating formula. Easier to read too.
15. (08:40) SORT Dynamic Array Function to sort the transportation prices from smallest to biggest.
16. (09:10) Summary of formulas and functions used in video.
17. (09:25) Summary, Closing and Video Links
How to accomplish if you do not have Microsoft 365. Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates, • Excel Magic Trick 967:...
Lookup 3 smallest values. Retrieve three smallest values. One Lookup value, return multiple records. Three Lookup Values. Three Way Lookup. Extract top values, Lookup and display lowest bids. How to lookup and return multiple values. To 10 values. Top three values. Top five values. Transportation Lookup Table. Complex Lookup. Three lookup values to get lowest supplier bids.
![[ GUILTY GEAR STRIVE: DUAL RULERS ] Teaser Trailer](http://i.ytimg.com/vi/e4TDqaE3XAg/mqdefault.jpg)
I watched this video 5 times to understand each part of the formula. Thanks Amazing Sifu Mike!
That is what is great about videos, there is a rewind button to help us learn : ) : )
Mike is the BRUCE LEE of excel
I have to watch his all videos at 0.5x to understand completely😎😎😎
Glad I can help, simplysagar!!!!
You are genius Mike, I love the Xmatch trick better than filter with {1,0,0,0,0,6,0,0}
Glad you like it : ) Yes, the XMATCH to filter columns is a great trick : )
Perfect mix of formulas
Glad you like it, Chris!!!!!
Lots of concept cleared. Now I know how to filter the filter.
Flad the filter filter helped : ) Sorta fun : )
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher !!!!!
Wow. This formula combination is amazing.
Glad it was ghostly amazing for you, kiwikiow!!!!
WOAH !!!! at minute 6:20 = Awesomeness Mike !!! I´ll be honest, I was not aware of XMATCH, I kid you not! 🤯🤯 #GoTeam !!!! 🔥🔥🔥🔥
Yes, FILTER is amazing in that semi colon gets us the rows and commas gets us the columns!!!! XMATCH with a function argument array operation is rad!!!!
Amazing video. Indeed, Excel Is Fun 🤩👍👍🤠
Thanks, Abiola David!!!
Wow i think i will study it more the once because the logic within in so versatile.
Thanks a lot Mike for another great video
You are welcome, luca !! Study makes us smarter : )
@@excelisfun So true😊
Boom!Now That's What I Call A Super Cool Formula!!Simply Awesome...Thank You Mike :)
Thanks for the boom on the super cool formula, darryl : ) : )
Made my day! Simply Amazing!
Glad it is amazing for you, Deepak!!!!!
Wow... just wonderful Mike! Thank you!
You are welcome, Edge!!!
Thank you, the best... as always :)
Glad you liked it, Davor!!!!
Awesome! Thanks Mike. XLOOKUP and FILTER are so powerful. These have become "daily drivers" in my workflow. Combine these with XMATCH and all the classic functions and you can do just about whatever your mind thinks of in Excel.
I totally agree. I think I use FILTER even more than XLOOKUP too. Because FILTER just seems to be able to do anything. Lucky for us : ) : ) : )
I am not a novice in excel, but this is totally new. Great job!
Yes, it is definitely new and awesome! Glad you liked it, bondniko!!!
Very good video. You are the best.
Glad it was very good for you, Paulo!!!
Amazing! Mike love you, God bless you..
Glad it is amazing for you, usman!!! Thanks for the love!!!!
I like the Power Query solution the best because it's so easy.
I like easy. :-)
I like worksheet formulas in lookup situations where I am changing conditions often. But Power Query definitely is awesome!!! Do you think it is easier than worksheet formulas?
@@excelisfun In this case: yes. The right combination of unpivot-merge-group-index-... will do the trick. :-)
@@GeertDelmulle To me, long time formula guy, formulas seem easier. Easier is in the eye of the beholder sometimes ; )
Awesome Monday lookup goodness Mike! Thanks for stepping through this great problem and super solution :)) Thumbs up!!
You are welcome for the step through. Thanks for the thumbs up : )
Although I have to admit that this video complicated for me, it was amazing. Thank you!
You are welcome! Watch a few times and study and you will have most of what you need for advanced lookups : ) : )
Epic formula Mike, outstanding!
Glad it is epic for you, Chris : )
Amazing as usual !
Long time no hear - great to hear from you Kevin : )
Super nice tricks. Thanks Mike
You are welcome, Nader!!!!
Thanks Mike. That was mind blowing!!! : ) : )
Glad it was mind blowing, John!!!
Formulas rule!!!!! ; )
Great Video!! I was missing this brilliant formulas videos !!😂✌. I've liked the ingenious way of using hybrid formula with LET inside.👍👍
For fun:
=LET(x,XLOOKUP(F30,I16:L16,I17:L26),y,(F17:F26=F28)*(G17:G26=F29)*x,z,IF(y,y),sm,SMALL(z,SEQUENCE(F31)),h,MAX(IFERROR(sm,"")),SORT(FILTER(CHOOSE({1,2},E17:E26,y),z
Amazing, Exceλambda!!!!! Thanks for the teammate awesomeness : ) I added the formula to the download workbook so that the Team can be happier and smarter : ) : )
Cesar, your formula is the best!! :-)
@@GeertDelmulle Thanks a lot!! Coming from you means a lot to me.😊✌
@@Excelambda I agree : ) : ) : ) : )
You are always awesome...🙏🙏
Glad to help, jai!!!
It was a great question!
Incredible, as usual...
Great problem and solution. :)
Yes, it is a fun problem!!!! Go Team!!!!
Great fun exercise! :-)
Fun is good!!!! Thanks for those cool intros and outros : ) : )
@@excelisfun Thanks Mike. You're welcome.
BTW: I just sent you my PQ-M solution to this EMT. Hope you like it.
This is one of those videos that will require more than one viewing to fully understand everything that is going on.
Yes, a lot going on. But less complicated that formulas before Microsoft 365 : )
@@excelisfun Mr. mike a have a question in regards to arreys.
if in o365 i type: "={1\2\3\4\5}" I'll get an array spilled into 5 columns .
now what If i need to change those hardcoded numbers to relative cell references?
i tried:
=INDIRECT("{"&A2&"\"&B2&"\"&C2&"\"&D2&"\"&E2&"}")
and
=INDIRECT("={"&A2&"\"&B2&"\"&C2&"\"&D2&"\"&E2&"}")
to no avail.
any hints how can I achieve inserting relative cell references into an array?
@@ExcelInstructor I do not really understand your question. For relative cell references, can't you just use: =A2:E2
@@excelisfun yeah I could. im just wondering if there is a way to create arrey syntax like: {1\2\A2\A3\3\4} so that some parts o the array are static and some are dependent on the cell value.
@@ExcelInstructor I do not know how to do that : ( I am not that smart, though. You can tray THE best Excel question web site for back and forth dialog to get Excel solutions: mrexcel.com/board
Good stuff. Intriguing. 🤔
Oz!!!!!!
Intriguing is good : )
Excellent mike
Glad it was EXCELlent for you, kishor!!!!
I was trying to figure out how to make the filter function dynamic
With rows. I like how you used xmatch and is number. Thank you.
I would love to see a video of how you solve a problem and see your trial and error process.
Wow! Amazing video. I only wish I could do that lol
You can do it!!!! Just go slow and build it piece by piece : )
just found the answer! amazing !
Yes!!! with over 3,600 videos posted over last 14 years, I have answers to many questions, as long as you search my channel : )
@@excelisfun I follow you for more than 8 years and I thank you very much for your efforts! It’s amazing
@@hectorricardus Glad to help! Thanks for the support with those thumbs ups : )
nice video as usual , was expecting the power query explanation
Not going to do that Power Query version because a scenario like this needs to be instant update and only formulas do that. But I did put the Power Query solution in download workbook : )
Thanks for you amazing tutorials I love and learned a lot from them. Do you also teach programming courses as well love your teaching style
I am sorry I only have Excel worksheet formulas, M Code and DAX programming at this channel.
It's Super EIF !!
Glad you like it, roderick!!!
I have a question on macro that involves calculation with moving range of data (all in numbers).
At stage 1, the range of data is A3:M51. (49 rows in all)
calculation for cell "I3" is "=COUNTIF($B$44:$H$51,A3)"
and calculation for cell "M3" is "=RANK($L3,$L$3:$L$51)”.
Then, “I3” and “M3” are to copy down all the way to “I51” and “M51” respectively.
Stage 1 ends here.
Next, stage 2,
2 sets of fresh data are added into A52:H53, and J52:K53.
Data in row 3 and 4 (A3:K4) are now no longer involved in this stage 2 calculation, but calculation is still on 49 rows. i.e. the data range had moved down, hence the new calculations are:
A
1) calculation cell "I3" changed to cell "I5" and
2) formula for "I5" is changed to "=COUNTIF($B$46:$H$53,A5)"
B
1) calculation cell "M3" changed to "M5" and
2) formula for "M5" is changed to "=RANK($L5,$L$5:$L$53)
And this (moving of data range down 2 rows) is to repeat and continue.
Can you suggest a way to record a macro to perform stage 2 and every subsequent stages after fresh data are added into the 2 bottom rows and the 2 rows of oldest data at the top are dismissed? Thanks.
Bonus #1. 7:15 is it faster or more efficient to use the if statement instead of just a min function? min(countifs criteria, hurdle)
Yes!!!!!!! That is a hot tip : ) LOVE IT!!!!
Hey I've taken your class before and It made me realize I really enjoy using excel for finances and just helping with life in general. I was wondering if you had a recommendation for what degree would be good for somebody pursuing a career using excel, finances, data science, and python. I get that its a bit of a request but your videos have helped me a lot and I really appreciate it. I really believe I would love pursuing a degree for this purpose but its so hard finding a route that covers all of this. I have a plan for completing college without student loan debt. I should probably talk to an advisor and I will, but I thought you would have a better idea than an advisor about this specific niche. If you do respond thank you so much, if you don't have a good day!
Finance uses Excel 100% of the time. It is the field that uses it the most. Accounting is second. Those are both of the majors that I earned.
@@excelisfun This makes a lot of sense, thanks a lot for letting me know!
if you please , how to shifting rows in power query??
Very useful. Thanks a lot.
Pl help me out to sort out the following error in power query.
I have a large data consisting of various formulae. Few headers include special character like backslashes. When i tried to transform this using power query, it works at the first time. But when i refresh it, it is throwing an error external table not in expected format. How do i sort out this.. Pl help
Try this great dialog to get solutions web site: mrexcel.com/board
This is amazing Mike ... and i have a question on excel CSV files ... once i used it for dynamic arrays formula it worked fine but it doesn't save ... so if i close and open ...everything is converted to values and no formulas .... any suggession ?
I am sorry but I do not understand your question.
@@excelisfun i used Excel .csv extension file … and inserted Filter formula … it worked fine … but after saving and closing then reopen the file again …the formula isn't there … only the values of the formula
@@HusseinKorish That is because a .csv file is NOT an Excel file. When you open a .csv file in Excel, it just uses Excel to display the .csv file. When you use Excel to open the .csv file, you can not add Excel features to that file. If you do, they will always be lost when you save it. If you open a .csv file in Excel and want to add formulas and use other features, you must Save As (F12) and save it as an Excel file.
@@excelisfun Thanks Mike ... that saved me lots of work
Hi Sir.. just need ur help..
I need to find the total sell plan for products that are priced greater than 15000 and less than 15000
Data set field are in this order
Product, Brand, Selling Price, Sell Plan
* is for logical AND, what's the equivalent for OR
+
Yes, like Jim says, plus is for OR and multiplication is for AND. Usually in school, this is taught in a statistics class.
Usually taught in statistics. Didn’t know that. Or, more likely, didn’t remember that from the last statistics class I took - 46 years ago! 😀 Good stuff - among the most useful courses I took.
*UNRELATED. I CHOSE YOUR MOST CURRENT VIDEO SO YOU SEE THIS QUESTION SOONER*
So. I your tutorials I notice you use fact tables with only one date column. What if I have a fact table with more than one date columns. How do I go about creating date dimension in power BI and modelling it and creating visuals. for example, I have an rdbms where the fact table has [order date], [shipping date], AND [required date]. I wish you could do a tutorial on it since all the answers I am getting from forums are all confusing. some say i should connect the date dimension to all date columns in the fact table and use something called USERELATIONSHIP in my DAX measures but i have no clue how to do that
Maybe this video from my MSPTDA class: MSPTDA 30: Order & Ship Dates in Fact Table: DAX Formulas and Data Modeling to Create Reports, ruclips.net/video/HJ9NMJpqJjE/видео.html
could you please make a formula for find negative and positive same values which set off each other with in same series.
Thanks in advance!
Example
series amount
1501 -100
1501 100
1501 -100
1501 100
1501 -100
1501 -100
2501 100
2527 -100
gbpq 100
gbpj -100
Try this great dialog to get solutions web site: mrexcel.com/board