Whatever Happened to the @@ Function?

May 27, 2022 - by Bill Jelen

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.

Numbers in A1:D8. Put the text C1 in F2. Use a formula of =INDIRECT(F2) and it returns the value stored in C1. — Figure 496. 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.

Using INDIRECT to point to a specific worksheet. — Figure 497. 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?".

This article is an excerpt from Power Excel With MrExcel

Title photo by Towfiqu barbhuiya on Unsplash