Excel INDIRECT Function: Lookup Values in Different Sheets / Excel Tabs

Подписаться 2,7 млн

Просмотров 624 тыс.

50% 1

Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
Learn how to use INDIRECT to manage data across different tabs and make your Excel tasks simpler. Whether you're a beginner or looking to enhance your skills, this tutorial has you covered.
⬇️ Download the workbook here: pages.xelplus....
✅ What You'll Learn:
▪️ The basics of INDIRECT function: Understand what INDIRECT does and how it simplifies Excel formulas.
▪️ Practical examples: See how INDIRECT works with SUMIFS function for dynamic data management.
▪️ Tips for dealing with different data tabs: Discover how to use INDIRECT when your data is spread across multiple tabs.
▪️ Error handling: Learn how to avoid common mistakes and understand error messages.
▪️ Advanced usage: Explore more complex scenarios where INDIRECT can be a game-changer.
▪️ Performance impact: Understand the limitations of INDIRECT in larger spreadsheets.
INDIRECT can be very useful in Excel Dashboards when you need a dynamic cell reference instead of hard-coding the reference in a formula. For example, let's say you have a drop down where the user can select for which year the revenue should be shown. Depending on the selection the formula with a SUMIF or SUMIFS function should sum up different ranges of data. Instead of writing a long formula with different conditions for each year that could be chosen, you can use INDIRECT.
Get the full Excel Dashboard course here: www.xelplus.co...
Indirect can be a confusing function. It takes a little bit time to get the hang of it. What Indirect does, is it returns an address. So for example, if you type in =indirect(A1) and inside A1, you have written A10 - then your formula returns what is inside A10. Why would you need this? Watch the full video and download the workbook to practice along.
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel

Опубликовано:

3 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 300

@LeilaGharani 8 месяцев назад

Grab the file I used in the video from here 👉 pages.xelplus.com/indirect-function-file

@kossiviamouzou7486 4 года назад

Leila is the QUEEN of Excel formulas. Like if you agree. I'm addicted to Excel and sometimes I think I know a lot but every time I'm blown away when I watch her videos

@Jenny19Marie 5 лет назад

I really appreciate the fast pace of your videos and that they are targeted to users with Excel experience. I feel like I'm getting a lot out of the 10 minutes spent on your videos instead of other videos showing baby steps that take loads of time to explain something simple. Loving your channel, thanks!

@LeilaGharani 5 лет назад

I'm really glad you like the videos!

@matthewruss1395 Год назад

@@LeilaGharani I was asked to try and create an inventory system for spare parts. That was easy. My challenge is to create a system that could macro quantity of parts and certain criteria of the parts requested another worksheet. Something like Amazon cart. I have a general idea of how to go about it. But would this be a cool video to create? Can’t find anything like it anywhere? (I want to say I’ve watched nearly every video and you are my hero and have carried me from my very basic job as a machine operator to a salary paid Operation Performance Lead helping so many get organized, developing sustainable systems and presentations that actually sell new and innovative ideas.)

@kossiviaglee2997 4 года назад

You cannot imagine how helpful are your tutorials for me. Thanks so much! May GOD bless you anf give back to you more than you freely give.

@19761999 5 лет назад

This is the first time I found one of your tutorials hard to follow and understand. No big deal, I was able grasp the indirect function by looking at other RU-vid videos. You're still my favorite Excel teacher by far. Thanks for all you do.

@LeilaGharani 5 лет назад

Thanks for the feedback! I'll try to improve on it.

@Tanushree00099 3 года назад

yes even i found it difficult and so i have worked around the net and found a very easy solution and i have updated it in the comment you can check up ..will be happy if it helps you.

@inpain537 2 года назад

I agree

@MAsif-pc5hj 3 года назад

You explained in such way that audience is already well known about function. There is a need to elaborate in simple way.

@davegoodo3603 4 года назад

Thanks Leila! I had no idea about the Indirect function, but after watching your video I have some idea. It messes with my head, which means to me that it’s worth learning. Thanks for this introduction.

@danieldavi 3 года назад

Dear Leila... I really need to say: "You are simple the best"... Thank you!!!

@LeilaGharani 3 года назад

Thank you! 😃

@CharblMakhlouf-zi9gl Год назад

I use excel since 1997, u are the best teacher ever

@rayn1384 2 года назад

I think you are now my favorite person in the world.

@euniceesinam2573 4 года назад

Indirect saved me from frustration a few years ago. But this video has some interesting things that set me thinking about wild stuff to do. Thanks, Leila

@poojaketaruth2920 2 года назад

Leila, you are a gift to the world indeed❤️

@excelisfun 7 лет назад

Thanks for the INDIRECT to different Sheets, Leila!!

@LeilaGharani 7 лет назад

You're welcome Mike. I'm a big fan of your Indirect videos :)

@bensharratt8451 5 лет назад

THE BEST tutorials online - Thanks very much!

@LeilaGharani 5 лет назад

You're very welcome Ben. I'm glad you like the videos!

@leomason15 Год назад

I've been struggling to understand this for weeks. Thanks so much for the info!!

@prasannaprasanna9955 4 года назад

Thanks. Your way of teaching solved every bit of my confusions regarding INDIRECT function

@Galileo2pi 3 года назад

Leila, I met with your channel; this is a gif for me; you're so right. Thanks for these tips. More, please

@LeilaGharani 3 года назад

Thank you Angel! Will do!

@hidgik 6 лет назад

You must be a happy person. You know - being familiar with the very entrails of the program! God bless you!

@LeilaGharani 6 лет назад

Thank you! There is still so much learn... but that also makes me happy :)

@1760Andy 5 лет назад

It's a fantastic function..I used.it with Name Manager ...linked around 700 sheets and made simple dashboard..

@LeilaGharani 5 лет назад

Great. I'm glad you could apply it right away.

@lucianaruschel842 5 лет назад

I couldn't do your way, but for me this formula worked =indirect(concatenate(cell where the sheet name is going to be),"[name of column you want to get results]")) Thanks for your videos! They help me a lot!

@redouanegipsy 6 лет назад

Hi Leila , Excel is like a passion for me , and I have watched tones of videos, but yours are really outstanding , Honestly , you have done an amazing Job , and I visited your website too, and it looks so professional , well done , and looking forward to learning more from you David Redouane

@bohemaxxtum 7 лет назад

Hi Leila,thanks a lot for this great video.indirect is one of the most useful function of excel and you really explained it very well.ExcellsFun MIke and you are my favourites for excel learning.thanks again.

@LeilaGharani 7 лет назад

I'm very flattered and very glad to hear you liked the video. Mike is the best! I've learnt a lot from him.

@Nevir202 3 года назад

Thanks. the part I wasn't getting was how to stop the ' from ruining my formula, never considered putting it in quotes to make sure it was handled as text itself. I feel like I learned something important.

@1gopalakrishnarao 7 лет назад

Very informative, and very useful for the excel users/addicts/lovers. I am one of your great fan of watching your videos. You are doing a great job for the excel users and making it interesting to learn more and more. Hope you will continue your good work in the coming days too!!!!!!!!!!!!!

@LeilaGharani 7 лет назад

Thanks Gopala. Happy to hear that. I will do my best to put as many tutorials out there as I can :) Let me know if you'd like to cover any specific topics and I'll add to my list.

@loidaasar487 5 лет назад

Very useful and informative makes my work a lot easier

@HoppiHopp 6 лет назад

Thank you again! :-) This was so helpful. I used "indirect" to have my formula (inside a proper Excel table) look in the cell below the current row/cell and compare it to the current cell while avoiding #ref errors if I choose to overwrite or delete half of the raw data.

@LeilaGharani 6 лет назад

You're very welcome.

@muathsaraireh 6 лет назад

Great video, thanks. Would have been easier to follow if you used standard excel references for cells and sheets. Thanks again

@mahoo2010 3 года назад

you're the best ever done this 👍👍👍👌👌👌👏👏👏

@LeilaGharani 3 года назад

Thank you so much 😀

@johnnyfiver4274 5 лет назад

Rocket science is a prerequisite for this course.

@Spaulding57 Год назад

You are right😂

@rexbk 3 года назад

Nice one, Leila. I would imagine my Boss (very old one) would scream to me in agony when seeing this kind of formula in my works.

@LeilaGharani 3 года назад

😁

@BARTLANTV 3 года назад

I'm studying this for my spreadsheet in design..Nice...

@TonyDiaz. 6 лет назад

Subbed! I'm an advanced Excel user (well, at least on the VBA side), but didn't know about this, so awesome stuff!

@LeilaGharani 6 лет назад

I'm glad you found this! It can come in really handy. Thanks for the sub :)

@alineleticia7440 Год назад

This video was super helpful ! Thanks

@wojtekziolkowski4631 5 лет назад

Hey Leila, thank you for explaining this function on name of the range basis.

@LeilaGharani 5 лет назад

You're very welcome!

@kendrewt 3 года назад

excellent examples and teaching!

@LeilaGharani 3 года назад

Thank you! 😃

@54788963 5 лет назад

I came, I learned. Thanks Leila

@mohitpalsingh1422 4 года назад

Been following your videos for a very long time now n they are amazing and very informative, can you please make one video on how to use consolidate function to collate data in one sheet.

@sunilswt07000 2 года назад

Hello Leila ma’am I am one of your great fan watching your videos. It’s very helpful and easy to understand. But i have a query, can you help me ?

@mohd4401 7 лет назад

To say thanks is not enough, you are awesome, I really enjoyed, regards

@LeilaGharani 7 лет назад

You're very welcome. Glad you liked it :)

@excelymasoficial 7 лет назад

Excellent explanation :) LIKE!

@LeilaGharani 7 лет назад

Thank you Excel y Mas :) & nice to see you here!

@ngilsz3 3 года назад

Thank you .. you are very wonderful

@bamakaze 3 года назад

Thank you Leila!

@cornellm2985 Месяц назад

You are incredible useful

@Grandkidsmovies 7 лет назад

Honorable gracious Madam! I am impressed of you. Your teaching style is commandable. Kindly let me know how to extract data from other workshhet or workbook by using index match function please?

@LeilaGharani 7 лет назад

Thanks Azhar. I have two videos on Index & Match: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-F264FpBDX28.html and more advanced: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ontXHp9cwOQ.html. You can then combine the indirect with those formulas instead of Vlookup. Is that what you're looking for? I'll add it to my list of future videos.

@MohAboAbdo 3 года назад

Thank you very much, dear teacher.

@vikasdsem9914 7 лет назад

i always watching your video which help me understand excel formula well now I got knew quarry ,that's why I request u to make VBA classes video step by step ,I also practice for it

@LeilaGharani 7 лет назад

Hi Vikas - glad you like the videos. VBA is on the list :)

@sdsdsasdadsada624 3 года назад

Fascinating explaination

@baldeepbirak 5 лет назад

Clever way to sum various tabs

@sachinrv1 7 лет назад

Hi Leila, You have very well explained the function...

@LeilaGharani 7 лет назад

You're welcome Sachin.

@aksohal 6 лет назад

oSM Leila, i ♥ Love ♥ the Way you Explain every term....

@LeilaGharani 6 лет назад

Glad you like it Ankur :)

@babarali8430 3 года назад

Always great !

@boubou_edu6174 4 года назад

you're helping me a lot

@DavidJasonB 3 года назад

Really wondering why there isn't a million view!

@collengura 7 лет назад

For clarity, it would be more helpful and easier to follow if you use cell reference instead of named ranges in your examples. For advanced students that would be fine.

@LeilaGharani 7 лет назад

true :)

@banwarifageriaexcelclasses1447 7 лет назад

excellent tutorial ... expecting some more advanced tutorials on excel thanks.

@ajayv304 5 лет назад

Hi Leila, it would have been better if u said that sheets 2016 and 2017 are referenced with names Data_Py and Data_Current. But no issues. Thank you very much at your tutorial.

@himanshudalai1028 5 лет назад

Great video Leila ! I admire your videos for its excellent way of explaining the concepts ! Thank you so much !

@satishsharma3875 4 года назад

Explained in very detail, impressive... just wondering if you have to rate yourself on scale of 1 to 10 on excel skills where do u keep yourself ...maybe 15? or 20? :)

@ravikiran-jn9ek 2 года назад

This is the most complicated video ii have ever watched. I ran into more difficulties rather than simplifications

@monsterstream4420 5 лет назад

Awesome. You are very good Leila. Thanks.

@monologuesofaconsultant 2 года назад

Thank you again!

@enriquel2959 3 месяца назад

Very interesting video, it's an eye opener for us. I wonder if you could make a video or give any tips on how to put together multiple cells from different tables into a single Excel sheet chronologically organized. For example: Table 1 has the following data: EE number / End date / Start date / Company 00000123 / 31.10.2023 / 13.02.2023 / Mokso 00000123 / 31.12.9999 / 01.11.2023 / Tecny Table 2 has the following data: EE number / End date / Start date / Ann. Salary 00000123 / 24.10.2023 / 13.02.2023 / 60000 00000123 / 17.03.2024 / 25.10.2023 / 62000 00000123 / 31.07.2024 / 18.03.2024 / 62500 00000123 / 31.12.9999 / 01.08.2024 / 65000 Table 3 has the following data: EE number / End date / Start date / Hire Date 00000123 / 31.12.9999 / 13.02.2023 / 13.02.2023 These have to be merged into one sheet as below, creating the rows separately, with a front row for the month (period), highlighting the values that have been changed in the new row. This may look simple for a single EE, now think that this has to work for 2000 employees. Month / Employee Number / End date of record / Begin date of record / Company Name / Ann. Salary / Hire date Feb'23 / 00000123 / 24.03.2023 / 13.02.2023 / Mokso / 60000 / 13.02.2023 Mar'23 / 00000123 / 31.05.2023 / 25.03.2023 / Mokso / 62000 / 13.02.2023 Apr'23 / 00000123 / 31.05.2023 / 25.03.2023 / Mokso / 62000 / 13.02.2023 May'23 / 00000123 / 31.05.2023 / 25.03.2023 / Mokso / 62000 / 13.02.2023 Jun'23 / 00000123 / 17.06.2023 / 01.06.2023 / Tecny / 62000 / 13.02.2023 Jun'23 / 00000123 / 31.07.2023 / 18.06.2023 / Tecny / 62500 / 13.02.2023 Jul'23 / 00000123 / 31.07.2023 / 18.06.2023 / Tecny / 62500 / 13.02.2023 Aug'23 / 00000123 / 31.12.9999 / 01.08.2023 / Tecny / 65000 / 13.02.2023 I hope you take the challenge!

@Kavi-Rajan 5 лет назад

Hi Leila, I have just became a big fan of your videos. Thanks a lot. In this example it's pretty simple if we use : =IF($C$14=2016;SUMIF(Data_2016!$B$2:$B$16;Report1!B15;Data_2016!$D$2:$D$16);SUMIF(Data_2017!$B$2:$B$16;Report1!B15;Data_2017!$D$2:$D$16)) why do we have to use indirect ? in other words,can you say a situation where without "Indirect" we can't get easily the result?

@Krilunius 4 года назад

por fin me sirvió esta fórmula!!!!Thank u

@nthieuutc 2 года назад

Nice content. Thanks

@anshulb1986 4 года назад

Which is a better formula to make Excels lean and efficient. Index Match or Indirect Address?

@kossiviamouzou7486 4 года назад

Indirect is much easier. Index match a lot more arguments so It get confusing sometimes

@MrVikas28feb 7 лет назад

Super!. i like your explanation of indirect function.

@LeilaGharani 7 лет назад

Great! You're welcome Vikas.

@jamesconcepcion2182 6 месяцев назад

do you offer online course?

@siddiquiaalameenullah6263 7 лет назад

Great! Excellent explanation

@LeilaGharani 7 лет назад

Glad you like it.

@AbdulAzizYaqubi 5 лет назад

Thank you for correcting it, useful topic

@amanjajoo9662 4 года назад

Thank you very much

@nikhilchowdhary8919 3 года назад

Thanks for this Leila.. :D It helped

@LeilaGharani 3 года назад

You're welcome 😊

@tasfia5904 3 года назад

Hi, can you make a video on how indirect is used to refer to larger tables in other sheet and rows/columns of that table using "&" ?

@gregoryboyle4363 7 лет назад

Great tutorials. Love the new intro and outro!

@LeilaGharani 7 лет назад

Glad you like them Greg :)

@sudhirhiwale9784 5 лет назад

Thank you very much Leila.

@LeilaGharani 5 лет назад

You're very welcome.

@gregbernard7861 4 года назад

That is super slick.

@mariaalcala5159 3 года назад

Great video! Do you have a video explaining the choose function?

@gayanedu 3 года назад

Thanks for your tutorial video !!!. It is wonderful & you explain very clearly. Just want to know is there a way to referencing value in a closed Excel workbook using INDIRECT?

@maddymaddy3323 4 года назад

Hi Leila , Thank you for providing Knowledge transfer to worldwide . Would you please share the tutorial of Index and Match function using VBA from one sheet 1 to sheet 2 .

@GR-ly1cu 2 года назад

This video was great, but I wanted to use "Region" instead of "Division" in the formula, unfortunately I couldn't do it.

@Tanushree00099 3 года назад

i found a very easy way to solve this =SUMIF(INDIRECT(A2&"!B:B"),B2,INDIRECT(A2&"!D:D")). I have placed the report in this way - Year Category Total 2017 Game 227020 The SUMIF asks for range which is dependent on the Year (drop down )which is chosen. So using indirect function we are choosing the sheet which requires to be referred for the range.And the range is in column B:B in both the sheets.The criteria is category which can be any of the three which we choose through a drop down.And the sum range is there in column D:D in both the sheets. I have also added another level Year Division Region Revenue 2016 Utility South America 33681 Here I have used SUMIFS =SUMIFS(INDIRECT(A6&"!D:D"),INDIRECT(A6&"!B:B"),B6,INDIRECT(A6&"!C:C"),C6)

@ca.lokenderchauhan2605 4 года назад

Pls make another video on indirect function with more examples. It is difficult formula.

@Chox_0 4 года назад

How do you use Indirect Function to reference another workbook with varying sheet name and the cell value needs to match certain criteria?

@jackychawla5422 7 лет назад

finally I got what I really wanted

@LeilaGharani 7 лет назад

:-)

@idwtv534 5 месяцев назад

Can we not use =k15 in place of indirect? We any how have the text we wanted in it

@birasafabrice 5 лет назад

thanks a lot for this very informative and helpful video. best regards!

@joelbravo2654 3 года назад

Leila thank you for all the inside into the huge realm of excel. I have workbook that I need help much like the one you have on your tutorial is there anyway to get your guidance to complete it?

@JagtarSingh-pf1on 5 лет назад

superb, 👍👏

@LeilaGharani 5 лет назад

Glad you like it.

@dad_newk 2 года назад

I doubt this will get to you but I'm hoping against the odds. Is there a way to use the indirect function to create dynamic drop down lists. In my position, every month, I receive a spreadsheet from a dozen different HR locations with two different tables. Instead of opening each spreadsheet and extracting the information I need I want to create a drop down list. The first drop down would either select the spreadsheet I want or the first table and the second drop down would either be the first table or second table (not really sure how it would work). Each spreadsheet is uniquely named based on the region it is coming from and each table is also uniquely named. The headers of the tables are all named the same. I don't think this is possible because I can't make a simple drop down from a different worksheet.

@Souravjanarajiv 3 месяца назад

Is it possible to use that formula without tablet sumifs

@anv.4614 2 года назад

appreciated. Thanks

@balajimudekulam8135 5 лет назад

Need a video from different files too instead of sheets.. Kindly help us same! Thanks a lot.

@chandankala4461 6 лет назад

As expected.... Good Job....

@LeilaGharani 6 лет назад

Thank you!

@cptkirky 4 года назад

Bit of a newbie, is this the only way to pull from other sheet tabs with a helper cell? You mentioned Choose at 11:26, can this be used? Do you have an overview of that function? Am I just looking too deep, am I missing the basics here, is there an easier solution for my formula (=VLOOKUP((A1,'Sheet2'!$A:$G,5,FALSE)) can I not have Sheet2 in a helper cell on my main sheet and have excel use it to know where to look without other bedded functions?

@SanjayKumar-yx6gc 7 лет назад

This one was awesome example of indirect function.i am crazy to your explanation. Leila, is it possible without table? Thx

@LeilaGharani 7 лет назад

Yes it is. You can put in the tab name instead - at the end of the video I've a put a note that if you have spaces in the tab name, you need to put it in single quotation marks '. Glad the explanation is easy to follow - indirect is not the easiest formula to explain :)

@madhugokavarapu6672 10 месяцев назад

Instead of vlookup we can give the table name in the text string.Anyway thank you madam 😊

@chulopapi812 Год назад

Nice video. How to do it with an image in another excel file ?

@Wildlife_Unlimited02 3 года назад

Thanks for showing different examples. Can we use indirect function with file path ?

@manjunatham9899 2 года назад

Why we need to concatenate with revenue and division with the Vlookup function, Please do clarify this doubt ?? I am waiting for your answer for this.

@Conman0218 2 года назад

I wouldn’t expect her to answer as she posted this 5 years ago. It’s concatenated so the Vlookup formula references the correct Table Header

@stephenphan2761 5 лет назад

Good lesson, your voice is very impression. By the way, which tools are you using : screen cast and micro phone would be an advance to your quality. thanks

@LeilaGharani 5 лет назад

Thank you. I'm glad you like the tutorials. You can see my setup here: www.xelplus.com/resources/#gear