How to Use the INDIRECT Function in Excel | Lookup Functions

============================================================
============================================================
Many people are not aware that you can use the INDIRECT function in Excel to convert a text string into a valid reference. This is an important function for anyone who uses formulas and needs to insert text strings, such as names or other labels, without having to type them out directly. You can use the & operator to create text strings so you don't have to type out long references. In this video tutorial, we will learn how this works!

First, we will create a basic worksheet with some data in it. Then we will use the INDIRECT function to convert a text string into a valid reference. We will also use the & operator to create text strings. Finally, we will see how this can be helpful in real-world scenarios! Stay tuned!

We will work INDIRECT into three ways i.e. Cell and range references, Named Range References and Worksheet References.

Let's see INDIRECT Function's Syntax first;

=INDIRECT("Reference")

The INDIRECT function takes a text string as an argument and converts it into a valid reference. The reference can be to a cell range, or to a named range. The function will return the value of the referenced cells. If the cells are not found, or if they are empty, the function will return #N/A.

Let's take a look at each type of reference individually, so you can see how this works in detail. Before we get started, however, I have to mention that it is possible for references to change when copied or dragged with the fill handle (the small grey box on the lower right corner).

First We will use INDIRECT for Cell and range references;

Cell Reference:

=INDIRECT("A"&B$12)

This formula will return the value in cell A12, which is the result of concatenating the text string "A" with the value in B$12. The reference to B$12 is a relative reference, which means that it will change as we copy or drag it.

Named Range Reference:

=INDIRECT("Product"&A12)

This formula will return the value of the Product named range, which is defined as ='Sheet name'!$D$14:$F$18 (notice that this reference does not start with a $, because it is a named range). The Named Range reference will not change if we copy or drag it.

Worksheet Reference:

=INDIRECT("Sheet"&A12)

This formula will return the value of the Sheet named range, which is defined as ='Sheet name'!$A$12:$J$12. The Worksheet reference will change if we copy or drag it.

Now let's try the INDIRECT function with a range of cells;

=INDIRECT("A"&B:C)

This formula will return the value in cell A B:C, which is the result of concatenating the text string "A" with the range B:C. The reference to B:C is a relative reference, which means that it will change as we copy or drag it.

Follow along video to practice it with us.

Browse through the timeline below to watch the video;

Table Of Content
00:00 Introduction
01:20 INDIRECT Basics - Cell & Range Reference
07:30 INDIRECT Function - Named Ranges Reference
11:43 INDIRECT Function - Worksheet Reference
14:40 Conclusion

In this in-depth video tutorial, I'll show you how to use the INDIRECT function in Excel. We will take a look at three ways that it can be used: Cell and range references, Named Range References, and Worksheet References. Keep an eye out for the syntax of each one!

Other Playlists:-