This is by far one of the most valuable Excel tutorial videos ever made. There are so many good examples on how to use this formulas that we can do all sorts of things with them. Many people know how to use individual formulas but few people have the skill and creativity to nest them together to simplify complicated operations in Excel. All of this is still valid ten years later in 2020.
Woo Hoo! Beautiful array formulas! Next week I have a long video just on the many uses of FREQUENCY function! In this one we will see many beautiful array formulas!
Try these: Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula
Great video but was missing something I was hoping to see. Can you combine the index and large function so you only need one formula to find the name of the largest value? Instead of 1 formula/column to find the largest value then a second formula/column to find the matching name?
What if you want the second largest but the values are not in a contiguous array? Instead of specifying a range, can you just list the cells individually?
Hi I applied this formula on a data set where I have to lookup text. so I am not understanding why we are using small. (9:15 minutes in the video). but the formula works fine but i am not understanding the logic
Hi! When you have completed the formula in E15 you said that because it was an array I must "Control Shift". As I live in the UK, I am not familiar with this term. Is the "Shift" key the same as "Return"? What does this do? I must be doing something wrong because when I drag it down, I get "191" in all 5 rows. Hope you can help!
sir your formula works fine as long as the list has at least 5 values in it.but,let say my range is dynamic That means, it can grow or shrink as range increase or decrease then wat formula i should use to sum large values.is there any way that i can make my formula dynamic for adding large value?
Hi, i was wondering if you could help me. I have a FX, if the value is false.I wanted it to assigned #1-10 based what is missing in the list. I'm thinking them small and row will work but i have tried any different ways and can't get it to work. Do you have any suggestions?
Hello, Can you help me please with a formula to sort excel rows independently. I have a table with 1500+ rows which i'd like to sort ascending. Thank you very much.
I have a similar issue that I just cant get to work. I need to pull from a list of names based on two separate criteria (a specific position indicator and the value of 1 being true) ... I can get the index function to work but can't seem to overcome the duplicate names being pulled from the list. =INDEX($B$2:$B$68,MATCH(1,($M$2:$M$68=1)*($A$2:$A$68=U17),0)) I tried using this video's concept but haven't had luck.. any suggestions?
![[TF2] The Russian Bear Trap](http://i.ytimg.com/vi/YFNSwz2_rOA/mqdefault.jpg)
This is by far one of the most valuable Excel tutorial videos ever made. There are so many good examples on how to use this formulas that we can do all sorts of things with them. Many people know how to use individual formulas but few people have the skill and creativity to nest them together to simplify complicated operations in Excel. All of this is still valid ten years later in 2020.
Woo Hoo! Beautiful array formulas!
Next week I have a long video just on the many uses of FREQUENCY function! In this one we will see many beautiful array formulas!
No, it is just problems with RUclips. You will have to try later.
Thanks for watching.
When I am watching your lessons, I understand that I don't know Excel! You are the best Excel specialist!! Thank you very much!!
I am glad that you like the videos!
Nice 👍🏻👍🏻👍🏻👍🏻
Thanks for the great formula, it's resolved my issues in my office work.
God bless you
Try these:
Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula
Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula
Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula
When it comes to Excel, you are my man,! thanks!
Glad the videos help, Brian X! Thank you for the comment, Thumbs Up and Sub : )
Thanks very much! I can't wait to do the FREQUENCY video later in the week!
Great! That looks like a great formula!
Sir thank you very much 👍👃
I agree BEAUTIFUL array formula.
مرسی
thank you for this wonderful video.
Awesome video as usually. I am looking forward for the frequency masterpiece.
EXCELlent!!!
Legendary as always!
Great video but was missing something I was hoping to see. Can you combine the index and large function so you only need one formula to find the name of the largest value? Instead of 1 formula/column to find the largest value then a second formula/column to find the matching name?
Sir can you teach how to analyse Stock Data to identify Higher High and Higher Low and Lower Low and Lower High
Check out this video title:
Excel Magic Trick 914: Top 5, Bottom 5 Excluding Zeros: LARGE, SMALL & AGGREGATE Functions
Can you show how to exclude zero values? So the next min number will show up instead of 0
What if you want the second largest but the values are not in a contiguous array? Instead of specifying a range, can you just list the cells individually?
Could you please explain same trick in power query
how to use small function in a sub total function or sub category when given a filter
Dear, how we can leave zero by using small function
super blast ....
Hi, i used this formula, it is showing same value in all rows But when i press f9 in function bar it is showing Correct Value
Hi I applied this formula on a data set where I have to lookup text. so I am not understanding why we are using small. (9:15 minutes in the video). but the formula works fine but i am not understanding the logic
Hi! When you have completed the formula in E15 you said that because it was an array I must "Control Shift". As I live in the UK, I am not familiar with this term. Is the "Shift" key the same as "Return"? What does this do? I must be doing something wrong because when I drag it down, I get "191" in all 5 rows.
Hope you can help!
sir your formula works fine as long as the list has at least 5 values in it.but,let say my range is dynamic That means, it can grow or shrink as range increase or decrease then wat formula i should use to sum large values.is there any way that i can make my formula dynamic for adding large value?
Hi, i was wondering if you could help me. I have a FX, if the value is false.I wanted it to assigned #1-10 based what is missing in the list. I'm thinking them small and row will work but i have tried any different ways and can't get it to work. Do you have any suggestions?
man, thanks a lot for this video, keep on it!! (Y)
Hello,
Can you help me please with a formula to sort excel rows independently. I have a table with 1500+ rows which i'd like to sort ascending.
Thank you very much.
I have a similar issue that I just cant get to work. I need to pull from a list of names based on two separate criteria (a specific position indicator and the value of 1 being true) ... I can get the index function to work but can't seem to overcome the duplicate names being pulled from the list.
=INDEX($B$2:$B$68,MATCH(1,($M$2:$M$68=1)*($A$2:$A$68=U17),0))
I tried using this video's concept but haven't had luck.. any suggestions?
love this formula, but when I change the data set to a table and use the named range instead of cell reference I get an error
Did you ever find a solution to this?
thanks for help
อย่างเทพอ่ะ +1
Does anyone know if you can do the Large/Small formula with multiple arrays?
Yes, like: =LARGE((L45:L46,L50:L51),2)
12 years later ; )
@@excelisfun legend status suits you. Much appreciated!
@@rgportocarero7889 Excel is fun so I am always happy to try and help : )