Beyond VLOOKUP: Using Names & INDIRECT Function

Using named ranges has several benefits:

• Formulas are easier to follow if they use logical names instead of cell references.

• If your ranges change size, grow in length or some other dimension, you can easily change the definition of the range to accommodate the change. If you do not use names, you would have to change the cell references in all of your formulas.

• You can use the INDIRECT function to refer to ranges

Naming Cells/Ranges:
Highlight the cell or range of cells and then type the name in the cell reference box.














INDIRECT FUNCTION
This function is so powerful, so useful and so underutilized, I am afraid I might not be able to do it complete justice.

1. Indirect will allow you to reference individual cells by building a reference within a formula: Want to retrieve the value in Cell A1? Use this formula: =INDIRECT("A1")
   
2. The true power of INDIRECT comes from its ability to build a name. Put the name of an variable or range in one cell and then add a number to it: Example Cell B9 contains RANGE and you want to reference RANGE2 =INDIRECT(B1,"2")
3. Building names is very handy when using functions like VLOOKUP that refer to ranges or arrays of data. See example below with two ranges of data: RANGE1 = B3:C7 & RANGE2 = E3:F7