VLOOKUP for Defined Ranges


« Back to videos

Additional Information:

The simplest and most commonly used form of VLOOKUP has three parameters; the entry you want to lookup, the table in which it will be found, and where in that table it will be found. It is entered as "=VLOOKUP(Entry, TableRange, TableColumn)". TableColumn can be confusing. Excel will always look for the Entry in the first column of the table. This is implicit in the use of the function. Therefore, TableColumn should be the number of the column containing the output data. In the video, the percentage is in the second column, where the item is in the first, so TableColumn equals 2.

This video demonstrates how to define a range by typing "_Percentage" directly into the address cell in the top-left corner of the sheet. Should you wish to edit this range at a later date, you will need to use the Name Manager. In early versions of Excel (up to 2003), this appears under Insert->Name->Define. Since Excel 2007, this option has appeared under Data->Defined Names->Name Manager.

In each case, you have the option of selecting the desired range, writing a new address under "Refers To", and confirming the change. This means you can extend lookup tables without having to edit any VLOOKUP formulas.