## The Opportunity

##
By using the Array VLOOKUP() it is possible to enter and maintain the VLOOKUP() for a range of cells in one place. The array VLOOKUP() returns all of the data for the selected range.

## Comparison of VLOOPUP() and Array VLOOKUP() Arguments

##
There are three arguments that are identical, one that is different

**Sought Value**: The value to be found in the first (leftmost) column of Searched Table, the second argument.
**Searched Table**: The contiguous range that consists of a leftmost column where the Sought Value is expected and one or more columns with data that might be returned.
**Results **
**Column (non-Array VLOOKUP)**: The column number within Searched Table where the returned value will be found.
**Columns (Array VLOOKUP): **The column number*s* within Searched Table were the value*s* to be returned will be found. The Column Numbers are grouped within a pair of curly brackets, i.e. "{}".

**Next Value:** A boolean to determine what to return if the Sought Value is not found within the Searched Table: "False", return an "#N/A!" error; "True" return values from the next row of Searched table.

**Sought Value**: The value to be found in the first (leftmost) column of Searched Table, the second argument.**Searched Table**: The contiguous range that consists of a leftmost column where the Sought Value is expected and one or more columns with data that might be returned.**Results****Column (non-Array VLOOKUP)**: The column number within Searched Table where the returned value will be found.**Columns (Array VLOOKUP):**The column numberwithin Searched Table were the value*s*to be returned will be found. The Column Numbers are grouped within a pair of curly brackets, i.e. "{}".*s***Next Value:**A boolean to determine what to return if the Sought Value is not found within the Searched Table: "False", return an "#N/A!" error; "True" return values from the next row of Searched table.

## How to enter the Array VLOOKUP()

##
- Select the range on the worksheet where the VLOOKUP() will be in effect. At minimum, select one row where the formula will be used for a VLOOKUP()
- Enter the formula as you normally would except for these exceptions
- When entering the 3rd argument (the offset or column for the result) enter a left curly bracket, then each of the columns/offsets separated by commas, closing with a right curly bracket. For example,"{2,4,6}" without the quote marks will return the 2nd, 4th, and 6th column of the table (2nd argument.curly.
- When done entering the formula, instead of hitting the ENTER key, hold the CONTROL and SHIFT keys while clicking ENTER.

- Drag-copy the row(s)/column(s) with the array formula to fill the needed range

- Select the range on the worksheet where the VLOOKUP() will be in effect. At minimum, select one row where the formula will be used for a VLOOKUP()
- Enter the formula as you normally would except for these exceptions
- When entering the 3rd argument (the offset or column for the result) enter a left curly bracket, then each of the columns/offsets separated by commas, closing with a right curly bracket. For example,"{2,4,6}" without the quote marks will return the 2nd, 4th, and 6th column of the table (2nd argument.curly.
- When done entering the formula, instead of hitting the ENTER key, hold the CONTROL and SHIFT keys while clicking ENTER.
- Drag-copy the row(s)/column(s) with the array formula to fill the needed range

## Maintenance Impacts

Maintenance, in this case, is updating the formula(s). In all of these examples it is assumed there a series of columns and each column has the same VLOOKUP() arguments.#### Non-Table scenario

- Updating a spreadsheet of VLOOKUP()s requires editing each column's formula then copying it to the rest of the column.
- Updating the Array VLOOKUP() is done by selecting the range where it is used then editing it

#### Table Scenario

- Updating a Table of VLOOKUP()s requires editing each column's formula. Excel takes care of the column consistency/
- Array functions with multi-cell results cannot be used within a Table

## No comments:

## Post a Comment