Excel: Whatever Happened to the @@ Function?

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. 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.

  2. 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.