How can I write a single vlookup function that returns multiple values to the right, instead of a single value, which causes me to write many vlookup functions in many columns.
This tutorial does not teach vlookup, that feature is taught in the Introduction and Advanced classes. Here we focus on vlookup and array formulas. The purpose of this tutorial is to supplement the class notes.
When performing calculations in Excel, typically a single cell contains a formula with functions that return a single value. An array formula is different in that it will typically include many cells (rows or columns) and return many values. Where I would teach students to press the enter key to commit an edit, to create an array formula we are required to hold Ctrl+Shift+Enter.
To follow along with this example, please download a workbook with this link:
Though not required, I suggest when you open this workbook, click the Enable macros command which will create two windows and tile them side by side.
In this example, the idea is to write a single vlookup function on the VlookupOrders sheet. The single vlookup will find the order number on the Data sheet and return three values, the Company Name, the Country and the Sale.
Example Of Data In Vlookup Array Workbook
1) On the VlookupOrders worksheet tab, select/highlight cells C2:E2
2) Create the following formula:
=VLOOKUP(B2,Data!B:F,{3,2,5},0)
Note: For the third argument, the col_index_num, we are allowed to use as many values as needed. In this example I am trying to illustrate that as much as I can return three columns to three cells — in any sequence — we can choose any number of columns as needed. However, to vlookup four columns, in step 1, select/highlight four cells. Also note that we cannot simply add parentheses around the values of the col_index_num, use curly braces {} (squiggly brackets – typically found to the right of the letter P on your keyboard).
3) To convert this to an array formula, hold the Ctrl+Shift keys and press the Enter key. The array formula adds {} (curly braces) around the vlookup formula.
A final word of warning: Array formulas have drawbacks. One drawback is that array formulas recalculate a bit slowly over thousands of rows. A bigger drawback is that we have lost the ability to insert a new column within the arrayed formula range. To verify the loss of the ability to insert a column inside the array, on the VlookupOrders sheet, right click column D and left click the Insert command.
