Problem: Back in Lotus 1-2-3, there was an @@ function. If you used @@(A3), Lotus would go to A3. A3 was supposed to contain a valid cell reference. Say that A3 contained the text C5. The @@ function would then return the value from cell C5.
Strategy: In Excel, this is called the INDIRECT function. Here are a few examples of how it works.
In the simplest case, consider a formula of =INDIRECT(F2). Excel will go to F2 and use the cell address found there. In the following figure, the answer in F4 first looks to F2 then to C1.
- F2 says to look at cell C1.
The cell reference in the INDIRECT can be calculated on the fly. In this example, the VLOOKUP points to a different worksheet based on the quarter number in column B. INDIRECT uses concatenation to build something that looks like a worksheet reference.
- Q2!A1:B99 is calculated on the fly inside of the INDIRECT.
Additional Details: If you have used range names, the value inside of INDIRECT can point to a range name. This creates some interesting lookup possibilities. For an example, see "Why Use the Intersection Operator?" on page 145.