Broad Strokes
Reference formulas allow you retrieve data by referencing the location (and/or contents) of other cells
Reference formulas are a good trick to have up your sleeve if you happen to be performing repetitive tasks on similarly formatted blocks of data, or if you need to create smarter lookups. These formulas also offer some interesting possibilities when they are paired with named ranges (such as tables), and can also be useful as a source of unique index numbers to associate with a range referenced in an array formula.
Usage and Style
Reference formulas are a worthy addition to your Excel arsenal
The selection of reference formulas in Excel leaves little to be desired – my only caveat is that you won’t see opportunities to use these formulas unless you’re aware that they exist. If you practice using them for simpler tasks, you’ll find them at your fingertips in the situations where they could really save you some time.
Syntax
Click the following buttons to read more about each formula.
Returns the cell/range at the given cell address
Returns a cell reference as text, based on cell coordinates and sheet name
Queries the row or column number of a given cell address
Queries the number of rows or columns in the given range
References a cell or range based on its offset and size relative to a given reference
Examples
The excel file embedded below demonstrates the use of these formulas. Note that these formulas are expansive in scope and may prove to be useful in a variety of circumstances.
All of the example formulas listed here look up data from the named table at the top of the sheet.