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



Easy explanation of Excel’s INDIRECT Function – How to use it and when to use it. Also what to watch out for when you’re using it.

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:

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.

Download the workbook here:

★ My Online Excel Courses ►

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

Get Office 365:
Microsoft Surface:

GEAR
Screen recorder:
Main Camera:
Backup Camera:
Main Lens:
Zoom Lens:
Audio Recorder:
Microphone:
Lights:

More resources on my Amazon page:

Let’s connect on social:
Instagram:
Twitter:
LinkedIn:

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!

#MsExcel

Nguồn: https://mechoimages.com/

Xem thêm bài viết khác: https://mechoimages.com/tong-hop/


Article Categories:
Tổng Hợp

Comments

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

    PRASANNA PRASANNA June 16, 2020 7:28 am Reply
  • Thank you very much

    AMAN JAJOO June 16, 2020 7:28 am Reply
  • Pls make another video on indirect function with more examples. It is difficult formula.

    CA.LOKENDER Chauhan June 16, 2020 7:28 am Reply
  • I just did not understand the note at @11:03

    Burak K. June 16, 2020 7:28 am Reply
  • 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 .

    maddy maddy June 16, 2020 7:28 am Reply
  • Hi I need a help with one formula to solve my ranking problem following various conditions. Ranking basis country wise.country 1 gets inportance over country 2. Rank basis Delivery charge offered by country 1 supplier than country 2 supplier. It there is tie between supplier than ranking basis lowest delay charge cost. If there is tie at delay charge than rank basis no of years in business.

    bikash ram June 16, 2020 7:28 am Reply
  • 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.

    mohit pal singh June 16, 2020 7:28 am Reply
  • 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?

    Chris Kirk June 16, 2020 7:28 am Reply
  • VERY NICE MAM
    THANKS
    OM SAI RAM

    AMIT KUMAR SHARMA June 16, 2020 7:28 am Reply
  • Hello Leila,
    Could you help me regarding this issue? I tried using the indirect formula for different workbooks. I have multiple file with only the month name different. Like jan report. Feb report. The indirect formula referencing to the text name of the file works. But i need the file to be open. Can you work around that and manage to fetch the data when the file is closed?

    Mohammed Aldighaithir June 16, 2020 7:28 am Reply
  • When i was using indirect function after vlookup it was giving error

    ABDULLAH SIDDIQI June 16, 2020 7:28 am Reply
  • por fin me sirvió esta fórmula!!!!Thank u

    Krilunius June 16, 2020 7:28 am Reply
  • Hello,
    Can I make a drop down list to take data from another excel file using indirect function?

    Chani Shah June 16, 2020 7:28 am Reply
  • Dear Leila, thank you for your great tutorial, I have a question. Is there any way to use date in indirect function as date instead of number? Whenever I use indirect formula combine with date, it indicates the date as number like 43922 but my sheet name is 01.04.2020 and I can not create a correlation. What I mean is when I wrote a formula in a cell =INDIRECT("A94") I get a result as 01.04.2020 Thats ok but when I wrote the formula as ="'"&INDIRECT("A94")&"'" I get the result as '43922' instead of '01.03.2020'. Is there any way to get the result as '01.04.2020'. Thank you in advance.

    Tolga Abbasoglu June 16, 2020 7:28 am Reply
  • احسنتي

    Shakira Asfoor June 16, 2020 7:28 am Reply
  • That is super slick.

    Greg Bernard June 16, 2020 7:28 am Reply
  • 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? 🙂

    Satish Sharma June 16, 2020 7:28 am Reply
  • Does anyone (Leila 😀 ) know if it possible to somehow wrap this with an IFNA() ?

    Example: {=VLOOKUP(A1, '\SBS2011RedirectedFoldersSOME_NAMEMy DocumentsSOME_FOLDERPO_SHEETS[FILE_LOOKUP.xlsx]Sheet1'!$A$1:$D$500, {2,3,4}, FALSE)}

    Thanks

    Giovanni Girelli June 16, 2020 7:28 am Reply
  • Integration between excel file to google sheet changes in any one file update any where between google sheet and excel file

    Sanjay Barge June 16, 2020 7:28 am Reply
  • Please give me solution. Like sync sheet excel add in

    Sanjay Barge June 16, 2020 7:28 am Reply
  • 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.

    Kossivi AGLEE June 16, 2020 7:28 am Reply
  • Imagine she got even 1% of our earnings for all she teaches the world how to do our job?
    I just got a job with a workbook with INDIRECT – and i'm like "WHAT?"

    Robert Long June 16, 2020 7:28 am Reply
  • 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.

    Dave Goodmanson June 16, 2020 7:28 am Reply
  • Hello I want the same concept but instead of getting on cell. I want the formula to reference the whole row. How can I achieve this?

    Verky June 16, 2020 7:28 am Reply
  • Hi. I use Mac's Numbers. It doesn't have a name manager. Do you have a work-around for this.

    Mark Sibert June 16, 2020 7:28 am Reply
  • if you are working on hundred sheets, better not to name the ranges. Just use the original range name (Column:column, or ro:row), as lon as your sheets are of same format

    eduardo sy June 16, 2020 7:28 am Reply
  • Hi dear Leila, what is the best combination of AVERAGEIFS in case of using multiple rows and columns criteria together??, please help and advice,

    TharAllah EbnThareh June 16, 2020 7:28 am Reply
  • A little thing annoys me with using this function is excel always ask for save when close file even though I didn't change anything after open.

    Hoàng Lâm Lê June 16, 2020 7:28 am Reply
  • Suppose I Have about 50 sheets with different names each having unique item number under a particular date within it.
    Queries:
    1) I want all those names to appear in the summary sheet without having to type those manually one by one.
    2) I want the data on those multiple sheets to sync with the summary sheet as well .And I want them to be arranged datewise and itemwise in the summary sheet as well.

    Rajarshi Basu June 16, 2020 7:28 am Reply
  • Leila, can you kindly share a video where i can find the last value in a dynamic column and row across multiple sheets?

    Loida Asar June 16, 2020 7:28 am Reply
  • Very useful and informative makes my work a lot easier

    Loida Asar June 16, 2020 7:28 am Reply
  • This video needs to be updated. It could've been explained better. You shouldn't need the helper table in H15 with Indirect.

    L VR June 16, 2020 7:28 am Reply
  • I love this!!!

    samuel kodjoe June 16, 2020 7:28 am Reply
  • Hi Leila – I am using INDIRECT to fetch a cell values from another workbook/s. It works fine while the other workbook is open, but turns to #REF when the workbook is closed. Pls suggest a workaround to this to keep the values in my workbook updated real time referencing the other workbooks

    Rohit Khopkar June 16, 2020 7:28 am Reply
  • It's a fantastic function..I used.it with Name Manager
    …linked around 700 sheets and made simple dashboard..

    Anand Gujarani June 16, 2020 7:28 am Reply
  • superb, 👍👏

    Jagtar Singh June 16, 2020 7:28 am Reply
  • Thank you for correcting it, useful topic

    Abdul Aziz Yaqubi June 16, 2020 7:28 am Reply
  • Bad video, it is not visible

    Abdul Aziz Yaqubi June 16, 2020 7:28 am Reply

Leave a Comment

Your email address will not be published. Required fields are marked *