INDIRECT Function in Excel - Powerful Range References

The INDIRECT Function in Excel is one of the building blocks for powerful and dynamic formulas and functions and, in this tutorial, I show you exactly how it works, starting from basic examples and moving up to a more useful implementation of INDIRECT that allows you to create dynamic formulas.

This tutorial will also give you the skills you need to understand and build more complex formulas using this function.

I hope you find the tutorial useful! :)

[uzu]

Couple of suggestions with your final example:

Dropdown list:
Rather than hard coding the list in, a more dynamic way would be to reference the cell range for the column headers you already have typed at the top of the table data. Eliminates the chance of a typographic error.

"Dynamically Constructed Range" instead of "Static Range Name":
Biggest fault with using Range Names is they are static in nature and don't allow for easy growth in a table as it evolves. Instead, better to use helper cells to dynamically construct the tables range (Start & End Row & Column values) and use INDIRECT() in conjunction with table searching using the column headers to achieve the same result. Benefit is additional columns and rows can be inserted into the table without the need to then go & manually defining new additional ranges to be used.

[greginfla_]

46, 000 views and only 850 thumbs up....that's rude

[allabout]

Thanks, simple but clear introduction. Exactly what I was looking for.

[dcoy]

Thanks so much! My indirect wasn't working and I didn't know why until I saw you use the name range manager

[lucysam]

Hello Sir. TeachExcel, How are you? Happy New Year. I found this video and was extremely excited to learn the format that you shared. I do have a query if during the month and or week, I transfer money from checking to savings and or received refunds from purchases, can you share how the formula can be modified to do so? I really appreciate and look forward to seeing that video.
Sincerely
New Viewer

[largpack]

Sorry to tell you, but the indirect function is really not recommended to use. Its a highly volatile function and can hit the performance of a workbook really badly if you use it to often. You can rewrite any indirect formula with the index-match or choose function very easily.

[wayneedmondson]

Hi.. thanks for this tutorial on INDIRECT which is a super cool and super useful function. I see a lot of Internet chatter about not using it because it is volatile.. which is true.. but as long as you know that fact and unless you are using it excessively in a very large or complicated workbook, it is a non-issue, particularly with how fast processors are these days. You can do so many fun and interesting things with it. Thanks for sharing your tips. Thumbs up!

[slomoichye]

How can format a number for instance in cell A4 saying unit if the value is equql to one as one unit, but if it's bigger then one saying units?
Thank you

[sachinrv]

Not much in use but still can do wonders when used with name, vlookup etc.

[abhinavsingh]

This was so helpful and loved the humor in between. Thanks !!

[analirpisani]

Well explained liked the min max part. Context of scenario where you can you indirect. Thanks for sharing

[wilmarkjohnatty]

YOu lost me at "anything with an equal sign is a formula". I realized i had to move along.