Of course, with dynamic array functions you can go on to write VLOOKUPs like this, e.g. =VLOOKUP(LARGE(A5:A9,{1,2,4}),tablearray,2) and return multiple answers in multiple cells.
@@TeachExcel Yes, thanks - lots of cool possibilities! In the course of additional playing around with the above formula I see there is a problem, however: if, for example, 2 records are both the 2nd largest, only one entry in the 2 column will be returned. Thus if two students' test scores are tied for 2nd place, only one student name in the 2nd column is yielded (the same name twice). Don't know of a workaround, but FILTER with RANK will return both student names.
You're welcome! And was just going to mention FILTER - its just amazing!! In fact, I love it so much that I try not to use it for now for fear of forgetting how to do it the 'old' way haha - almost no one I send files to has anything beyond Excel 2016.(
Thank you for this upload! Is there a way to report ALL instances of a max or min value. E.g. If I have a dataset that has a max value of 20, but that value appears 17 times throughout the dataset, is there a way to find all 17 rows that it appears at?
I was wondering if there could be a way to return the row number of the output from Vlookup. With an extra column indicating row number I made it. Although this is away from the point of the subject your lesson gave me a useful hint.
I've a group of patients(pts) taking many medications (each pts taking 4+ medications). I want the excel to calculate the "nearest date of discontinue medication" for each drug in relation to another date "date of surgery". How can I do that?
You have no idea the world you are opening up for me. I still like the easier parts of your tutorials best.
I'm very glad to hear this Kathy :))) Keep learning and practicing and you will be an expert in no time!!!
what if the numbers are not on the most lest side.. what formula can be use?
Bro your teaching skill so good
Of course, with dynamic array functions you can go on to write VLOOKUPs like this, e.g. =VLOOKUP(LARGE(A5:A9,{1,2,4}),tablearray,2)
and return multiple answers in multiple cells.
Love it! Especially when you combine it with new functions, you can do so many cool and complex things with ease!)
@@TeachExcel Yes, thanks - lots of cool possibilities! In the course of additional playing around with the above formula I see there is a problem, however: if, for example, 2 records are both the 2nd largest, only one entry in the 2 column will be returned. Thus if two students' test scores are tied for 2nd place, only one student name in the 2nd column is yielded (the same name twice). Don't know of a workaround, but FILTER with RANK will return both student names.
You're welcome! And was just going to mention FILTER - its just amazing!! In fact, I love it so much that I try not to use it for now for fear of forgetting how to do it the 'old' way haha - almost no one I send files to has anything beyond Excel 2016.(
@@TeachExcel Thanks. Yes, FILTER is amazing and I also have to deal with people who don't have it!
Thank you for sharing!
You're very welcome Ivan!
Thank you for this upload!
Is there a way to report ALL instances of a max or min value. E.g. If I have a dataset that has a max value of 20, but that value appears 17 times throughout the dataset, is there a way to find all 17 rows that it appears at?
Thank you for this sir 🙂
You are very welcome :)
Great Tutorial With Some Really Cool Formulas...Thank You Sir :)
Thanks Darryl! You are very welcom!))
I was wondering if there could be a way to return the row number of the output from Vlookup.
With an extra column indicating row number I made it. Although this is away from the point of the subject your lesson gave me a useful hint.
I'm glad you got it working! :) Helper columns can be AMAZING! And you can also hide them if you don't want the user to see them.
Thanks so much
Excellent 🤩
Even with it’s known issues (sorted, no left lookup, etc) VLOOKUP is still one of the most powerful Excel functions
Definitely! And it will be around for a veryy long time since most people still can't access the newer functions.
I've a group of patients(pts) taking many medications (each pts taking 4+ medications).
I want the excel to calculate the "nearest date of discontinue medication" for each drug in relation to another date "date of surgery". How can I do that?
Dear sir,
I want average of four number excluding upper and lower number.
Example
10, 20, 30, 40
Average is 25
Aint the next highest value to 4.6...5
Why it went for 3 in ascending
And 1 in descending