# INDIRECT function: example to illustrate this function

Posted by Kristel on January 07, 2002 1:54 AM

i'd like to explain this function to my students but i can 't find a real good (and rather simple) example. Maybe some of you do have one?
Thanks for writing it down for me!

Posted by Aladin Akyurek on January 07, 2002 2:55 AM

Hi Kristel --

Example 1.

In A1 enter: E1
In E1 enter: 60
In A2 enter: =INDIRECT(A1)

Now, name cell A1 MaxValue via the Name Box on the Formula Bar.

In A3 enter: =INDIRECT(MaxValue)

Rather trivial, but it shows how it "works."

Example 2.

In E2 enter: 100

Example 3. (Non-trivial)

Define three tables: CARS and Renault and BMW.

CARS is a 1-column table/list, consisting of just 2 entries:

{"Renault","BMW"}

Now create a 2-column table whose 1st column houses some Renault-models and 2nd column their prices.

Name the 1st column of this table "Renault" via the Name Box.
Name the whole table RenaultPRICES.

Now create a 2-column table whose 1st column houses some BMW-models and 2nd column their prices.

Name the 1st column of this table "BMW" via the Name Box.
Name the whole table BMWPRICES.

In A6 create a drop-down list via Data|Validation using List and CARS as Source [ =CARS ]

In B6 create another drop-down list via Data|Validation, using List and

=INDIRECT(A6)

as Source formula.

In C6 enter: =VLOOKUP(B6,INDIRECT(A6&"PRICES"),2,0)

The last example presupposes that your studs know a lot.

Hope this helps.

Posted by KRISTEL on January 07, 2002 3:15 AM

Thanks a lot but...(i'm very demanding today...)what I really am looking for is a real-life example to show that this is a really interesting function to use in every day life. My students are of the kind that always ask why and when can WE use this and thsi time I can 't find a good example myself...

Posted by Aladin Akyurek on January 07, 2002 3:44 AM

Isn't Example 3 a real-life example? You could also adapt this example such that you can retrieve prices of products and compare them, for example, "Supermarkten" vs "Winkeliers".