Learn the INDIRECT function of Excel with these 5 INDIRECT function examples.

See how to use INDIRECT with named ranges, to reference other worksheets, create R1C1 references and see it used with other functions.

Download the file to follow along here –

The INDIRECT function is one of the most misunderstood of the functions of Excel. It can be really powerful when used correctly. These 5 examples will show you different reasons as to why INDIRECT is thought of so highly.

Below are the timings for the different tutorials in this video.

00:38 – INDIRECT with Named Ranges
03:26 – INDIRECT to reference other worksheets
07:52 – Using R1C1 referencing with INDIRECT to return the last value in a row
12:15 – INDIRECT with VLOOKUP
14:40 – Dependent Drop Down Lists

Got any questions. Please post them in the comments area.

Master Excel today with this comprehensive course –

Find more great free tutorials at;

Connect with us!
LinkedIn ►
Facebook ►
Twitter ►

Nguồn: https://phanrangninhthuan.info/

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

43 Comments

meteora8888

July 3, 2020

thanks. nice and simple to understand examples.

Reply

Radhakrishnan Radhakrishnan

July 3, 2020

Sir i have one doubt
1-apple
1- banna
One common refernce but vlookup only one view 1- apple but i have need two output- apple/banna how
do you do sir

Reply

Radhakrishnan Radhakrishnan

July 3, 2020

This is very useful

Reply

Debbie Fagan

July 3, 2020

Hi Great Video (as they all are) but please can you explain why in INDIRECT to reference other worksheets when you wrote the second part of the Indriect after the & you put " in then not again until after the range of cells?? Sorry if I am being thick but I though " " were to refer to text. Thanks

Reply

Mekonnen Yohanes

July 3, 2020

Thank you!

Reply

Mohamed

July 3, 2020

the best explanation of indirect fnc . love it. going to watch so many more now <3 !!!

Reply

omar bebars

July 3, 2020

In this formula =INDIRECT("Sheet"&A1&"!$A$5") , What if I need cell A5 as variable "A5 , A6, A7" from different sheets . What will the formula look like . Thanks in advance

Reply

Denin Srmic

July 3, 2020

I was really not that versed in using =INDIRECT(function) and to be frank I was avoiding to use it. But after well expounded topic in this tutorial it makes a lot of sense what is used for and especially when it comes using it with named ranges. Thank you for explaining in minute detail and to the point for all of us. 👍

Reply

Penny Wang

July 3, 2020

Thank you ! I've been sufferred from learning “INDIRECT" as it's not as clear logic as other fuctions. Watched around 3-4 videos still couldn't get until I watched your video. Now it's crystal clear and now I'm confident to use "INDIRECT" in work.

Reply

tommyishie

July 3, 2020

This is the best explanation of the INDIRECT function I have seen. Thank you!

Reply

richard mayorga

July 3, 2020

Awesome explanation of a far to underused function in excel! Thank you sir.

Reply

Mohammed Aldighaithir

July 3, 2020

Hello, thank you for the great video and elaborate explanation. I was wondering if you know of a way that can refer to another excel workbook while it is closed without getting the "Ref" answer.
I have many excel files that usually has only a different month name, and i want to pull the data from it just by changing the month name while it is closed. The indirect approach always asks for the file to be open to see the numbers.
an example is, if i have a file called January report, and i want cell B6 from it, then i also want to have B6 from February report.
referring by indirect has worked only when both files are open.

Reply

Ahmed Abdul Alim

July 3, 2020

Excellent video

Reply

Seshagiri Rao Pamuru

July 3, 2020

WHEN YOU ENTER SOUTHEMPTON B3 IT SHOWS DIFFERENT TYPE OF 155. HOW IT ENTERED L 155. PLS TELL ME SIR. MAIL ID IS tally1954@gmail.com

Reply

damdan akan

July 3, 2020

I feel that watching a premier leageu game :))))

Reply

Pongpunt Aaron T.

July 3, 2020

This tutorial is extremely helpful. Thank you very much.

Reply

Emmanuel Enemchukwu

July 3, 2020

I'm 8 minutes in and I already came here to drop a comment on how amazing this explanation is. Also thanks for a little British humor.

Reply

KINGO INFO

July 3, 2020

Amazing video l'm LEARN so much

Reply

Zakeer Rg

July 3, 2020

AMAZING. Both your detailed lecture and your accent. I have become a fan of yours. Can we have your real name please.

Reply

Jasvinder Jassel

July 3, 2020

Do you have this worksheet for subscribers to download

Reply

Mwamba Chilando

July 3, 2020

Great video, beautiful learning.

Reply

Milburn Grimes

July 3, 2020

You fixed the South Aftica reference by surrounding the single quote with double quotes but on on the front of the formula. After the second & you wrote double quotes + single quote. Why didn't you surround the second single quote the same way as the first one?

Reply

Rina Samanta

July 3, 2020

I want add two more value under Canada, that means Dynamic Name Range and want to get effect on Office.
I have create Dynamic Name Range using OFFSET(), but getting #REF error when I apply it with INDIRECT().
Please….

Reply

Poorani Gunasekaren

July 3, 2020

sir nice
can you explain pivot table

Reply

Paul Nagle

July 3, 2020

Very concise and easy to understand, how is your sister lady gaga ?

Reply

Hi Year 2050

July 3, 2020

thanks you very much

Reply

mahendran p

July 3, 2020

wonderful explanation about INDIRECT function, especially combining DATA VALIDATION and INDIRECT function was totally awesome…

Reply

Sameh Sameh

July 3, 2020

Excellent

Reply

Peter Compton

July 3, 2020

Great video. I've seen many explanations for INDIRECT() and this is by far the best, especially the one referring to other sheets with spaces in the sheet name. Thanks very much

Reply

I Nengah Sugita

July 3, 2020

thank you, it is really helpfull

Reply

Asir In Tisar

July 3, 2020

Thanks for making such helpful video….

Reply

Mahaboob Hossain

July 3, 2020

brother it is showing reference but why?????? my formula is correct according to your guide lines for calculation of indirect formula where this is =sum(indirect(my cel no#))—-the answer is showing REF. Can u solve for me or mail me brother or reply me for the solution plzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

Reply

siva Chandrasekaran

July 3, 2020

Thank you. Clear explanation. Amazed by different techniques!!!

Reply

Peter Ng'ang'a

July 3, 2020

Thank you, Computergaga. Very much appreciated. This Excel world is just becoming very exciting; courtesy persons like you.

Reply

E F

July 3, 2020

you are making everything so complicated , plus the reference example is not working

Reply

mad nor

July 3, 2020

awesome!!!
thank you so much mr gaga.

Reply

Sizzler Sizzle

July 3, 2020

Awesome.
Thanks u very much for such a detail and simple explanation.

Great Work..

Reply

mike obasuyi

July 3, 2020

Wow. I have struggled with this function in different forms and here you are making me an expert after watching this 👏🏿👏🏿👏🏿👏🏿👏🏿

Reply

tubeampsrule1

July 3, 2020

In the conditional table array example, how would I rewrite the Formula to find the person with the largest number for the region you specify?

Reply

FRANKWHITE1996

July 3, 2020

Thanks!

Reply

MDE SASF

July 3, 2020

Thank you for stepping through the Indirect formula. I've seen it a dozen times on as many sites, but I have not seen one explanation of each component of the formula. Much appreciated! Thank you!

Reply

Chelsea C

July 3, 2020

great example! can indirect be used in conjunction of SUMIFS ?

Reply

MaxDouglas UK

July 3, 2020

This is very informative and helpful!

Reply

Leave a Reply