This is an excellent tutorial. In one video you explained these lookup functions and how they can be used to refer to ranges dynamically. Wow 👏

You are phenomenal! These videos demonstrate so well 1) how these functions can be utilized and 2) how to actually implement them. Honestly, its so incredibly helpful. I appreciate it more than you could even know.

This is what every Excel user has to learn, two years ago I didn't know anything about these functions, but now everything is different, thanks Mynda.

Dear Mynda, I appreciate the way you are teaching as you gives us an overview that I am aware that I have not missed somethings. Excellent. Thank you.

Amazing, I have never thought of using Lookup functions like this. This totally change how i am currently working with all these functions.

Perfect !! Thanks a lot for the Excel updated formulas information.

Excellent tutorial with some nifty tricks with the combination of formulas!

Great summary and insights :) Thank you.

What an excellent teacher. Thank you for explaining in detail.❤❤❤❤

You are so knowledgeable. Awesome job.

You are a genius and a superb teacher! You make it so easy to learn from your videos. Thanks for being so helpful.

Great Tutorial Mrs. Mynda

Perfect explanation thank you very much!

It is a greate help for me, thankyou very Much for Your unvaluable help

Thanks Myndy. I need to practice on my Offset. I hardly use it. Index and Match is what comes to my mind in similar scenarios. Looks FUN!!!

Complete and in-depth video of these functions and their respective analogies and differences. A single point: The functions IFS, SWITCH (and also MAXIFS, MINIFS, CONCAT and TEXTJOIN) they appeared in the 2016 version and logically were remained in the 2019 version. Exceptional level of explanation. Thank you Mynda!

Brilliant!!! I fought through a challenge to define a variable range based on prior years from 2010 to 2023 in a Futurevalue function I was writing the other day and I ended up using a combination of Indirect and Address to define the range within the function... This is MUCH easier and less volatile (using INDEX!). Thanks!

All jewels in one place, its so incredibly helpful.... on your own it is hard to get mastery on all these without guideline such as yours.

Hey, It's a very well collated video for the return of references. I am a fan of you and the way you explained not even here, also in udemy courses is amazing and effective. Now the choice of fastest method would become easy post this video..well done Thanks for sharing! I did your "Dashboard in one hour course" and found superb content that can be used in different ways. Great Leaning!!! Keep Sharing :)

Thank you so much; ultimate use 🙏🏼

Excellent as usual. I note that there are ZERO downvotes! Surely that's some sort of record for KZfaq!

Great video. I use Xlookup for charts on workbook data if the data is ordered. For example if I have a bunch of data for all companies, ordered "Company 1", "Company 2" etc, then I can create a dynamic chart from a company validation list. By using Xlookup normally I can get the first row of data for company 1, then using the start from bottom of range criteria in the xlookup I can get the last occurrence. I can use this xlookup:xlookup reference in a named range for reference in the chart and use the validation list to change company. Hope that makes sense!

Great Explanation 👌

Would be great if I had this version... Powerful Stuff!

O ho, wao. Where have you been when i was in college. I can't describe in words how much i have learned from this video. You are guru of excel for sure. 🙏🙏

Very useful tips, Thanks Madam

Fantastic video. Thanks

You nail exactly what people are looking for, most of your titles are unique. anyway thanks for the video

I like all your videos. IF function is immortal.

you are best doctor I like your method. keep going to top

Thank you so much for the wonderful tutorial. these functions are wonderful and helped me learn a lot. I wonder why not using the hard-coded method. It can be used in define name too.

Thanks for sharing...

Hi Mynda.. great video and lesson on functions that return references. All are great, but INDEX is still tops for efficiency and flexibility. I use each of these regularly, but pick which one depending on the circumstances. Thanks for all the great examples and insights. Thumbs up!! PS - Agree wth Reda below.. learning these functions and the tricks to using them opens up tons of options and possibilities. Thanks for sharing your wisdom and knowledge :))

you are way outta the league of the phrase "EXCEL GURU" the video explanation was understandable and precise Keep giving the value maam

Excellently explained! I wonder where to get this Excel T-Shirt...?

For me, index and choose are very incredible and miracle functions ...

The only good thing about indirect is when you have many same structured data (like Jan, Feb,...) then the indirect can be used to call the tab that you want on another tab. Indirect is a strange function and with difficult syntax. Nice video

Nice video mam , I use mostly match function for returning functions. And sometimes count and countif. Very good guide 👍. I also try to explain few tutorial but in other languages, hope you.will share your valuable feedback.

Thank you, 🏆🏆🏆useful

Well Done Myanda

Awesome video as per usual. Is there an advantage to creating a named range reference for a table with a formula over formatting as a table to auto create a named range? Also, one helpful adaptation for the index / match combo has been using * as AND operator to select on multiple column or row criteria. For example selecting data for a person in a particular year and location.

A tremendous and very jam packed video of excellent tips! Thank you very much! I do have a question about how you might return a reference to a table name? I have three named tables (one for each scenario that contains multiple rows and columns) and a space where I write the name of the tables in a column. I use indirect to refer to the table name so that I can select which scenario (table) I want and it pulls that representative table (scenario). But I had to write down the names (hard code) of all of the tables because I don't know how to return the name of a table. If I add tables for more scenarios, I have to be sure to add that table name to the list. God help me if I change a table name and don't remember to change my list of tables. Any thoughts?

Respect!

Super 👌 👍

Hello, Can you please show how to do the same for the partial match? For example return all rows and columns if category contains "ke".Regards.

Dear Mynda, Regarding the slightly more effective use of *INDIRECT()* to return a reference (a range of cells), one can take the following steps: (0) Select the range *B7:E7* , i.e. the range of data cells inclusive of the column headers. (1) Using the _"Create Names From"_ Excel shortcut *CTRL+SHIFT+F3* , define AUTOMATICALLY CREATED names for each individual vertical range of cells in columns B through E. This will result in 4 automatically created names *"Year", "Category", "Product"* and *"Sales"* . (2) Write in cell *B13* the name of the cells (i.e. the name of the range) which you want to display in cells *B14:B17* . For example: *Sales* (3a) If you're using Excel 365, write in cell *B14* the following formula: *=INDIRECT(B13)* and let Excel 365 spill the resulting array onto cells *B14 through B17* . (3b) If you're using Excel 201x, select the cells *B14:B17* , press the button F2 and write the same formula; however, this time enter it with *CTRL+SHIFT+ENTER* as an array formula. Voila! In case some column headers are made up of multiple words separated by blank spaces, then the formula needs to be changed to: *=INDIRECT(SUBSTITUTE(B13," ","_"))* since Excel replaces blank spaces with the underline character when creating names from column and/or row headers of a data table. I shall enter this as a comment below your blog post too. I have been following you together with my students since Spring 2016. And thanks to my students of the Spring semester in 2017, I also got the same *"I simply EXCEL"* t-shirt as yours. (:D

Is there any way to open multiple hyperlink link at once? Example open FB, YT, twitter from hyperlink link pasted in excel with just one click or short cut keys

Hi Mynda very nice thanks for video. Can you please confirm which version offset is working i am using 2013 and its showing error #VALUE! also INDIRECT is not working in 2013

I'm definitely a self-taught excel user and I don't know much. I was wondering if you could help me out. I am making a spreadsheet that will have multiple sheets with similar data, the difference will be that individual employee will have an assigned sheet and in that sheet will be what they accomplished throughout the year by month. I'll have a "Summary" sheet at the end that I wish to "pull" data from the other sheets to see particular information that I need at the time. For example, I want to see how Person A performed during the months of January - March. So perhaps a drop down list or something so that when I choose the name and month, the employee's month performance is displayed. Then i continue to do that for the other months and then average those months out to see "historical work ethic" as we call it. I don't know if I'm explaining it right but it almost seems like this video answers that question but I can't "see" it. I will rewatch this video a few more times to see if I can figure it out

Hello. I would like to ask how can i return a range of values from a reference x to reference y? Thank you!

Can you do a VBA playlist tutorials please ?

G.O.A.T

Can you explain what you mean when you say "return references" because to me they are just formulae that return a result. How are they different to a normal formula?

How are you making the formulas appear in the formula column?

👍💯

Assuming I want the cell reference for all the rows of the specific variable which is repeated in intermittent order...how do I do that?

What is the reason why the values ​​of the answers change when creating the same problem in excel in two method

Question unrelated to the video: How do you get the formula to show in the adjacent cell?

17:30 Can't believe you'd say that. You are completely ignoring what Indirect is FOR, which is custom building references. For example, if I want to get a running total down column A of the last 100 items in a list, I could do something like =SUMINDIRECT("A"&IF(ROW()=,100,1,ROW()-101)1:A"&(ROW()-1))) Which is dynamically building an array of all the cells above the current row and summing them, unless there are more than 100, in which case, it's just the last 100.

The title should be How to Perfectly Use the Functions , Thank you for providing such valuable information.