5 Excel INDIRECT Function Examples - Learn the Great INDIRECT Function

[@tommyishie]

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

[@Computergaga]

Thank you very much. Your comment is much appreciated.

[@jamesperry3837]

Thanks for the feedback. I did try to use "R10C4" and it works but only for that column and not for the next/expanding columns. So ur right it does make sense to use COUNTA. Thanks again.

[@jackychawla5422]

computergaga always comes with inspirational videos

[@bamakaze]

Maybe the best explanation of indirect on KZbin. Thank you!

[@Computergaga]

That's awesome! Thank you very much, Cary.

[@missongpeiqi]

Spent 3 hours trying to figure out the error with the spaces that causes #REF and this is the only explanation that helped. Thank you so much!

[@Computergaga]

You're very welcome.

[@jamesperry3837]

So today i got to look at INDIRECT + R1C1 + COUNTA. That was really good. Never seen all that being used at one go. Very interesting if u need to fetch the LAST VALUE for anything. Thank u very much for sharing.

[@Computergaga]

Your welcome James

[@cipher2]

An analyst at work used this a few years back for a spreadsheet I use daily, I always wondered over it, now finally taking the time to understand it. Thank you for this vid!!!

[@Computergaga]

My pleasure Cipher-Tu Productions.

[@jamesperry3837]

And finally the magic of INDIRECT + NAMED RANGES/Dependent Lists. I liked 4/5 of the INDIRECT function examples. Thank u for sharing.

[@AlokSinghimpact]

Excellent explaination to make use of indirect function.

[@Computergaga]

Thank you Alok.

[@iBenji1]

Amazing, I learned indirect with this short video. thank you !

[@Computergaga]

Your welcome Ben.

[@ca.lokenderchauhan2605]

Excellent. Best video on indirect funtion on KZbin.

[@Computergaga]

Thank you very much.

[@kendrewt]

Good examples and good explanations for indirect functions! You put in learners' shoes and lead us to see how indirect function helps us. Thank you!

[@Computergaga]

You're welcome. Thank you.

[@inpain537]

THIS IS THE BEST DISCUSSION OF INDIRECT FUNCTION! THANK YOU SO MUCH

[@Computergaga]

Thank you.

[@avinashjadhav2436]

This is a masterpiece, Sir!..... Agreed you have taught the world of this most useful function called , INDIRECT, I learnt in a very simple way, you voice is clear and audible plus has a very good command on the language plus the enthusiasm throughout the video was overwhelming.. Sir! Thanks a ton.... From India

[@Computergaga]

Your welcome Avinash, thank you very much.

[@juanitaburt5281]

Now I know how to use the various indirect functions. You explained it so well, thank you.

[@Computergaga]

You're welcome, Juanita. Thank you.

[@GeethMilan]

Went through many videos to land at this conclusive & the best video on INDIRECT ...thank u

[@Computergaga]

Thank you very much 🙂 Glad it was useful.

[@sachinrv1]

Sir, This is one of the most INFORMATIVE video on INDIRECT function. This is so useful making life of finance person like me a dam easy.. just amazing.. thanks for sharing...

[@Computergaga]

Your welcome Sachin, thank you.

[@georgebenz8536]

Echoing other comments - this is fantastic explanation. Can't wait to use this function.

[@Computergaga]

Thank you, George.

[@pinoymysteryman]

Thank You. I am beginner but I am learning. THANKS A LOT.

[@Computergaga]

Your welcome Matt, thanks.

[@selsset]

Reeeeeeally well explained. Thank you so much!

[@Computergaga]

You're very welcome. Thank you.

[@m.raedallulu4166]

An interesting demonstration, and very useful information. Thank you!

[@Computergaga]

You are very welcome.

[@tomaszstarzZz]

OMG, this is AMAZING! and so useful. Also well explained and demonstrated - subed

[@Computergaga]

Thank you Tomasz.

[@guitarvorous]

Awesome! Its explained in such a brilliant way! All thumbs up!

[@Computergaga]

Thank you, Yeasir.

[@mohideenthassim7180]

Loved it Alan, thank you for the great explanation of the indirect function. Cheers Mohideen

[@Computergaga]

Thanks Mohideen

[@ishaanrao4184]

You were of great help. Very clear explanation! Thank you :)

[@Computergaga]

Glad to hear that! Thank you, Ishaan.

[@MyMediaKH]

Your lessons so good.

[@Computergaga]

Thank you 😊

[@AbhishekDaga93]

Awesome function and wonderful explanation.

[@Computergaga]

Thank you, Abhishek.

[@jamesperry3837]

Watched the first 3 mins - INDIRECT with NAMED ranges. Pretty kool. Thank u. Instead of typing in Caridiff or Liverpool etc u could also use Data Validation ??

[@Computergaga]

Absolutely you can. Especially when you type like I do :)

[@zakeerrg3010]

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

[@Computergaga]

Thank you Zakeer. My name is Alan Murray.

[@anonymous_553]

@@Computergaga I'm having trouble understanding and making use of the following formula =if(not(isblank(B31)), indirect("Income!D"&B31&":Q"&B31)," "). I'm a beginner, can you help please?

[@madnor2028]

awesome!!! thank you so much mr gaga.

[@Computergaga]

You're very welcome.

[@shoaibrehman9988]

Sir, This is one of best videos very easy way to describe thx

[@Computergaga]

Thank you very much.

[@bharatgupta2858]

Great way to teach. Thanks

[@Computergaga]

Thank you Bharat.

[@axetaxe1]

This tutorial is extremely helpful. Thank you very much.

[@Computergaga]

Great to hear. Thank you.

[@obaidali1270]

Worthy fully watching Sir

[@Computergaga]

Thanks Obaid.

[@Murthyams]

Thats wonderful !!! Thanks again

[@Computergaga]

My pleasure Murthy.

[@zeinelmokhtar4105]

What a teacher!

[@Computergaga]

Thank you 😊

[@captainvlog]

Great video, thank you.

[@Computergaga]

You're welcome captainvlog.

[@bhaveshmishra9298]

Nicely Explained

[@Computergaga]

Thank you Bhavesh.

[@sabbasachisaha]

Really helpful for me.thanks a lot sir

[@Computergaga]

Great to hear Sabbasachi.

[@robsonbeckert380]

Thanks for sharing!

[@Computergaga]

You're welcome, Robson.

[@rajeshhariharan7575]

Thanks, very good explanation.. and I love the accent too :)

[@Computergaga]

Thank you! 😃

[@mwambachilando5580]

Great video, beautiful learning.

[@Computergaga]

Thank you Mwamba.

[@healthresearches563]

This tutorial is Amazing! Thank you. In the first worksheet 'with named ranges'; when I change the formula from SUM to AVERAGE, keep the rest same, the result i get is 523 instead of 588, which happens to be the correct answer. Please help understand this. Thank you

[@tubeampsrule1]

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?

[@Computergaga]

You may be able to do this with the MAXIF function.

[@tobyiy]

incredible! thank you so much

[@Computergaga]

You're very welcome!

[@mahansor8295]

Many thanks for this , great

[@Computergaga]

You are welcome! Thank you Mehran.

[@caseyzimmerman369]

excellent video!!!

[@Computergaga]

Thank you Casey.

[@raghabakumardalai2511]

Excellent explain

[@Computergaga]

Thanks Raghaba.

[@sir0611]

Hi Alan , thanks a lot for your great tutorial ! But I still not understand the part of 7:25 , the formula =sum(indirect("'"&B3&"'!C4:C11")) , epecially this part "'"&B3&"' , hope you can teach me again

[@ahming123]

Awesome!! Like your channel!

[@Computergaga]

Thank you Nephilibata.

[@willm7994]

This is amazing thanks 🙏

[@Computergaga]

Thank you, Will.

[@rogerdodger9855]

I'd really like to use the INDIRECT function to make dependent drop down lists but the selected cell references I need to use (customer name) in the first drop down list has spaces. I can't make a named range for the subsequent drop down lists with spaces. I'm very new to Excel. Do I have any other options?

[@Computergaga]

I have a solution in this video Roger - kzbin.info/www/bejne/l5rHgIqFqLKXepo

[@rogerdodger9855]

@@Computergaga WOW, thank you so much. Just watched it. HaHa, you wouldn't believe the solution I came up with between the time I posted my initial comment to you and coming back here to see you had replied with a link to a very excellent solution. Thanks again, I greatly appreciate you replying to me!

[@Computergaga]

No problem Roger.

[@rogerdodger9855]

@@Computergaga I do not mean to exhaust your kindness in regards to responding to me but I have what I think is a simple question for you to answer if you have time. I am using =LOOKUP(2,1/(A:A""),A:A) to get the value of the last cell in column A on the sheet. It works great as long as the formula is on the same sheet. Can this formula be used to refer to a column on a sheet that it is not entered on? Any recommendation or videos you have that I can utilize for this purpose? Thank you again, very amazing, thoughtful content!

[@1974GSM]

Very nice mini-tutorial. Unfortunately, you didn't cover the situation whereby your formula in Sheet2 addresses Sheet1!A1 which contains a reference to another cell with a Value you wish to obtain.

[@amanansari5567]

THANKS A LOT

[@Computergaga]

You're welcome Aman.

[@_mahmoudkamel4016]

Fantastic Many thanks

[@Computergaga]

Your welcome Mahmoud

[@naradaweerasooriya162]

How to get the column value? When inserting new rows each time

[@mohamedmami2480]

Thanks a lot for your explanations of all the ways of the Indirect function.. it is really useful and helpful. Actually, before you published this video I have an issue when making a drop-down list by using an Indirect Function. I followed all the steps you gave, but unfortunately it still not working with me and it always ends up with a message saying( The Source currently evaluates to an error, Do you want to continue?) Could you please help me with that because I really need to apply it ASAP? Thanks a lot,

[@Computergaga]

That error does not necessarily indicate a problem. You get that message when the cell that INDIRECT is looking at is empty. Try continuing and populating that cell. I can't advise further without knowing more.

[@mohamedmami2480]

Thanks a lot for your prompt answer. Could I send to you the worksheet and have look at it? if you have time Sir. Many Thanks,

[@Computergaga]

Sure email or FB message it across to me.

[@AhmedAbdalalim]

Excellent video

[@Computergaga]

Thank you Ahmed.

[@cdobza3390]

hi, COMPUTERGAGA. Ii have an excel with various sheets. In each sheet, I have a same text in a cell and this text has different references. can I pull all that data from different sheets and put it in a column by entering that text

[@SalmaMassoud]

Thanks a lot!

[@Computergaga]

You're welcome Salma.

[@SanjayKumar-yx6gc]

Thank a lot. This was a great video, as per my understanding, in 2nd example of indirect, we have different sheets, however, data should be same column, it' ok. However, if i have different sheets & data in different columns , then , how would apply indirect function? Is it possible through indirect? could you assist me? Thank you so much, taught us indirect function in excel. It would be great appreciated, if you upload more examples based on indirect with index, match & another functions. I hope so, you will provide us in future. Thanks, Sanjay.

[@Computergaga]

Definitely possible with INDIRECT but it would need some help. It can refer to the sheet as in my video but then would need assistance from functions such as MATCH to find which column to sum. We would use MATCH to find the column using its header. I'll try and produce a video on this soon.

[@lenac3587]

Great! I have subscribed and look forward to that video.

[@premshekhar]

excellent work

[@Computergaga]

Thanks Premshekhar.

[@chelseac7368]

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

[@Computergaga]

Sure. To reference other sheets, named ranges or tables.

[@lifecoach0823]

Great video, can I download your practice sheet from somewhere so I can practice when I was watching? Thanks

[@Computergaga]

Thanks Sabrina. There is a link in the video description to download the file used.

[@caseyzimmerman369]

coming back to this video because it is so great. i have shared this video with coworkers. i have one question that i am struggling with. i am trying to use indirect reference for a HLOOKUP within that named range. the named range has a space so the name had an underscore. the only way i can get the indirect function to match the cell with the range is using the substitute equation to swap the space with an _. this works, but is there a better way? i watched the part about referencing a tab with a space. but wondering how to reference a range with a space. for example cell A1 has "personnel expense" which i want to indirectly link to the personnel_expense named range.

[@Computergaga]

You did exactly what I was going to suggest. That is your best way. To nest a SUBSTITUTE function into the INDIRECT formula to swap the space for an underscore. Excellent work Casey, and thanks for sharing.

[@SolomonKinyanjui_sk]

Solomon Kinyanjui 1 second ago Hi Alan? I have tried to use indirect function with named range in the data validation it is working perfectly but if i substitute the named ranged with dynamic named ranged (i.e. using offset function) in the data validation it is not working. Would mind to explain why?

[@Computergaga]

I'm not aware of there being a problem with this Solomon. As long as the name of the dynamic named range and the items chosen from the list match up, INDIRECT will work.

[@jamesprosystems]

Hola como hago para conocer un precio si tengo 2 dropdown menus , para un productos que tiene varios tipos de licenciamiento como tabs, entonces la formula tiene que revisar el producto y la licencia para luego darme el valor exacto

[@alaaaliwi7321]

Thank you for your explain but I have a question if you can answer it please. If I have data base and I want to use Likert Scale from 1 to 5 and use data validation inside the excel table and I want to display labels instead of numbers inside the table (1= very unsatisfied 2= not satisfied .......5= very satisfied) So how I can use numbers inside the tables as data validation list but inside the cell I want to display the labels instead of numbers.

[@alaaaliwi7321]

If any one here can help me please

[@Computergaga]

You can use numbers in the Data Validation list - no problem. You will not be able to replace the same cell with a label though, not without a macro. Best bet is to setup a lookup table, and use a VLOOKUP in a different cell to return the label related to the selected number.

[@عثمانابوزياد-ث7ي]

vary good video

[@kossiviamouzou7486]

Hi I watched the video from the beginning and I follow along with the Exercise file. Everything went smoothly except the last example. Dependent drop-down. When i finish, the drop down arrow is not even working. I couldn't select. I tried and tried. Same result. Could you assist please.

[@kossiviamouzou7486]

I got it already. The spelling of named ranges are different. There was underscore. Now it's working fine. Thanks

[@Computergaga]

Awesome! Nice one 👍

[@ThinkOFood]

Thank you very much for the video. 🙌I have a bit different scenario and I am not sure which Function should I used. Hoping you could help me or direct me to one of your videos. I have two tables in two separate sheets in one excel file. One table is a recipe table where I have all the ingredients, measurements, and another column to reference the price of the product from the second table in a different sheet. I am consistently updating the second table and sorting it A-Z. When doing so the retrieved values are changing and all my pricing is going out of order. I tried to use the lock"$" but it does not seem to work. Could you suggest a function or a method to keep the referenced value from moving and at the same time staying the same value? VLookUp would not work as the names of the ingredients are not always the exact match to the ingredients table. Thank you, Andrey🙏

[@Computergaga]

You're welcome. You need some kind of lookup but Excel would need a way of matching the ingredients in the two tables. If the names do not match exactly we would need something else such as a Product ID. You can message me directly if you want.

[@ThinkOFood]

@@Computergaga Thank you very much. Yes, I came to a solution by creating a Vlookup formula to track a product ID and retrieving the price. Thanks, Andrey

[@allabout1135]

WoW Amazing!

[@Computergaga]

Thank you Aleksandrs.

[@mekonnenyohanes5287]

Thank you!

[@Computergaga]

You're welcome, Mekonnen.

[@michalroesler]

Cool video. Can INDIRECT be used to pass a string reference to a ROW function to display the row (I mean absolute row nmbr) number of some value? In example if I had "computergaga" in cell "C8", how would the function look like, which would return 8 in some other cell, lets say "F120". ???

[@Computergaga]

F120 would use a lookup formula for the value "computergaga". Either INDEX or VLOOKUP or others. Wrap it in ROW to return the row when it is found. INDIRECT converts a reference from the string, it cannot look for a cell value.

[@fidellamas1378]

Hello! Mr Computergaga. Your video is extraordinary and very well explained, and I really appreciate so much you have shared this knowledge, but I have a doubt about your explanation on minute 6:45 when you are explaining about the formula =SUM(INDIRECT("'"&B3&"'!C4:C11")) to accept the sheet names that have space such South Africa. I don´t really understand the function of the apostrophe ( ' ) and the quotation marks ( " " ). What represents each one in the formula. I made the formula as your explanation and it worked O.K but it doens't really make sense to me the meaning, representation and function. If you could given me an explanation of this, it'll be great. Thank you so much!! Fidel Lamas

[@Computergaga]

Thank you, Fidel. If a sheet name contains a space (or other illegal character), Excel puts single quotes around its name. So a sheet named Germany would be shown as Germany!. But a sheet named South Africa is 'South Africa'! due to the space. We need our reference in INDIRECT to have these quotes for that reason, so the double quotes are used to write that text literally. So we are combining the ' to the value of cell B3 to the '!. Hope that makes sense. It can be difficult to explain.

[@fidellamas1378]

@@Computergaga Hi! computergaga, than you so much for your explanation, now it makes more sense to me. Regards!

[@chinkonglam4290]

Hi! I am unable to download the workbook on indirect tutorial. Kindly advise. Thank you.

[@Computergaga]

This looked like a typo on the url. I have fixed this. Sorry for the trouble Chin Kong Lam.

[@FIntelligenceTV]

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.

[@Computergaga]

Hi Mohammed. It may be better to use a Power Query approach. You can use tat to import data from another Excel file and use a single cell value for the month name.

[@majidsiddique8227]

very smart!!

[@Computergaga]

Thank you Majid.

[@omarbebars8426]

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

[@Computergaga]

If you need to find the value becasue it might be A5, A6 or A7 you will need a way of finding it. What that is will depend on your scenarion. Maybe VLOOKUP or maybe INDEX.

[@CrazyGuy9990]

One caution on the INDIRECT function to count last row or column is that it won't work if you have blank cells in between cells with data

[@Computergaga]

Absolutely! 👍🏼 Thank you for the comment.

[@yupilyoyu]

gracias!!

[@Computergaga]

You're very welcome.

[@FRANKWHITE1996]

Thanks!

[@Computergaga]

You're welcome Frank.

[@gaoshikui88]

Love the accent!

[@Computergaga]

Thank you, Steven 😊

[@pooranigunasekaren]

sir nice can you explain pivot table

[@Computergaga]

PivotTable for beginners tutorial here - kzbin.info/www/bejne/l5Dco4ivpb-Dbbc

[@ziasiddique3172]

Thank you for making video. This function doesn't look very useful. By the time you have keyed everything in and checked the formulae you could have just used the sum function. There is a use for this function but it looks minimal at best.

[@Computergaga]

Using INDIRECT enables us to create dynamic references from what users may enter into cells, going beyond SUM capability. So a user could change what sheet, or range a formula sums from a simple drop down for example.

[@lenac3587]

Great examples Alan! This is the best demonstration of indirect function, so easy to understand, clear and how it can be used in so many ways which I hadn't known before. Now I know why indirect() is useful which I failed to gather from other channels. To add to the complexity of the last example, it would be great to show how to handle an office that consists of two words like Milton Keynes. :)

[@Computergaga]

Thank you Lena. I have a video on solving this situation. You can find it here - kzbin.info/www/bejne/l5rHgIqFqLKXepo

[@amitkumar-ip6yv]

Excellent video

[@Computergaga]

Thanks Amit

[@mdesasf]

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!

[@Computergaga]

You're very welcome 👍

[@kvlpnd]

You deserve alot more likes for this well explained video.

[@Computergaga]

Thank you Keval.

[@lisamccray6481]

@11:13+, you said A1 or C1 or (R1?) style reference. I'm not sure what that means but OK. It obviously is a column, OR row, OR all of the table thing. It feels like I am missing the point by just a small detail.

[@Exceltrainingvideos]

Excellent!

[@Computergaga]

Thanks Dinesh.

[@mannychuks8016]

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.

[@Computergaga]

Thank you Emmanuel. Your comment is appreciated.

[@kaushiksrivatsa]

TBH I have always hated this function, but not anymore. Thank You for this CG

[@Computergaga]

Your welcome Kaushik.

[@scottmckibben6286]

I don't often leave comments but this was so well done that I felt compelled. Kudos for clear concise explanations but my favorite part was the multiple examples that demonstrate real world challenges and the slightly different approaches you might need to take to solve them. Take a bow sir!

[@Computergaga]

Thank you very much for those lovely comments Scott.

[@bijuphilipjacob2961]

Thank you, Thank you, Thank you, Thank you, Thank you so much. You have no idea how useful this is to me in my daily work. Thank you ever so much. Kindly continue to contribute such excellent tutorials with excellent examples. God Bless You.

[@Computergaga]

Thanks Biju, your welcome.

[@sasavienne]

There must be a good reason why I easily understand what you explain than many other people who offer different tutorials. The reason is simply, you can explain things so well. THANKS INDEED.

[@Computergaga]

You're very welcome. Thank you for your kind words Salim.

[@1gopalakrishnarao]

Thank you very much. Very interesting, practical, inspiring, revealing and thought provoking. You are doing a great job for the excel lovers/addicts/users. Please continue your good job for the benefit of Excel users with some more tricks/tips/time saving tools. Yours affectionate.

[@Computergaga]

You can guarantee more tricks/tips and time saving tools Gopala my friend.

[@jasonjackson4555]

This is great! I had to populate data from several other tabs in a different file. You just made me look like a genius at work!

[@Computergaga]

That is great Jason. You are a genius :)

[@Excelrush]

Extremely well explained!

[@Computergaga]

Thank you Excel Rush.

[@harsinchh4342]

Hello Sir, A B C 1.20 40 30 2.(10) 50 [60 3. 20 30 20 4. 50 10 10 5. 60 10 80 6. 30 (90) 50] 7. 20 20 70 In above eg. A2 has minimum value & B6 has maximum value, so from this range 2-6 I want sum of column C2-C6 (60+20+10+80+50). so from the 1st column minimum value cell to 2nd column maximum value cell, I want sum of in-between cells of column C. I have 1000s sets each set of 7 of 3rows with 3 columns to execute in this manner. I m providing one more eg. so that u have better understanding. A B C 1. 50 30 30 2. 60 20 40 3. (30) 10 [50 4. 90 80 60 5. 70 (90) 30] 6. 50 70 20 7. 40 40 10 from min value of column A3(30) to max value B5(90), sum all cells of column C, C3 to C5 that is [50+60+30]. Please help with this question. Thank You.

[@courtneyvargas8879]

Can INDIRECT be used for dynamic rows, but tied to a grouping of those rows? So the row cell numbers will change, but the grouping does not?