Array VLOOKUP()

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

1. Sought Value:  The value to be found in the first (leftmost) column of Searched Table, the second argument.
2. 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.
3. Results 
• Column (non-Array VLOOKUP): The column number within Searched Table where the returned value will be found.
• Columns (Array VLOOKUP): The column numbers within Searched Table were the values to be returned will be found.  The Column Numbers are grouped within a pair of curly brackets, i.e. "{}".
4. 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()

1. 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()
2. 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.
3. 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

Disappointments

1. I can find no reference for either array LOOKUP() or array HLOOKUP().  My assumption is they are not implemented because they are so seldom used.
2. The 3rd argument, the offset, must be literal and not referential.  This means you must enter the column numbers, e.g. "{2,...}," and cannot enter a function such "{COLUMN(FirstNameColumn),...}"  See "Name the Source Columns" section of  in Improving VLOOKUP and HLOOKUP.
3. Multi-cell array formulas cannot be used in a Table.
4. If the Array VLOOKUP() is entered into one cell it cannot not be extended across the row using a drag-copy.