VLOOKUP Return Multiple Columns – Excel & Google Sheets

VLOOKUP Return Multiple Columns

This tutorial will demonstrate how to return multiple columns using VLOOKUP in Excel and Google Sheets. If your version of Excel supports XLOOKUP, we recommend using XLOOKUP instead.

vlookup multiple columns

 

VLOOKUP Array Formula

To VLOOKUP Multiple columns at once, you can create a single array formula (this section) or use helper columns (next section) if you don’t want to use an array formula.

 

To VLOOKUP multiple columns at once with a single array formula, populate col_index_num with an array and convert the VLOOKUP Function to an array formula.

{=VLOOKUP(F3,B3:D7,{3,2},FALSE)}

vlookup array col

 

Let’s walk through the steps in creating the formula:

  1. First, select the output range where the resulting array will be returned.

vlookup array formula step1

  1. While the range is selected, we go to the formula bar and type our VLOOKUP Formula.

 

  1. Next, we create our array input for col_index_num by enclosing the list of values with curly brackets ({}).

vlookup array formula step3

 

Note: To create a 1D Horizontal Array, we separate the values with a comma (,). For 1D Vertical Array, we use a semi-colon (;) as a separator. For 2D, we use a comma and semi-colon to imply a column and row, respectively.

For non-US Regional Settings, commas in formulas are replaced with semi-colons, and for arrays, we use the backslash (\) for columns and semi-colon (;) for rows:

vlookup array formula step3 backslash

  1. Complete the formula and press Ctrl+Shift+Enter for Windows (Command+Return for Mac), and the curly brackets will automatically enclose the whole formula:

vlookup array formula step4 close

 

Note: These curly brackets signify an array formula, and unlike the curly brackets for the array of values, these curly brackets cannot be typed and can only be entered using the above method.

 

Instead of creating an array manually, we can reference the col_index_nums from a range. This makes it more dynamic compared to the previous method. We can do this with a single array formula:

{=VLOOKUP(F4,B3:D7,G2:H2,FALSE)}

vlookup array formula columns parameter

 

VLOOKUP non-Array Formula

If you don’t want to use an Array formula, you can set up “helper cells” for the column references and Copy and Paste the formula across multiple columns:

=VLOOKUP($F$4,$B$3:$D$7,G2,FALSE)

vlookup array formula columns 1

Note: Make sure to lock the cell references of lookup_value and table_array.

 

VLOOKUP-MATCH Dynamic Multiple Columns

By using VLOOKUP and MATCH together, you can dynamically lookup the column headers for the col_index_num:

=VLOOKUP($F$3,$B$3:$D$7,MATCH(G2,$B$2:$D$2,0),FALSE)

vlookup match

 

Google Sheets: VLOOKUP Return Multiple Columns

All formulas that were mentioned work the same way in Google Sheets, but for converting formulas to array formulas, Google Sheets has a function that is dedicated to doing this – the ARRAYFORMULA Function.

We can type ARRAYFORMULA or press Ctrl+Shift+Enter to automatically enclose our formula with the ARRAYFORMULA Function:

=ArrayFormula(VLOOKUP($F$3,$B$3:$D$7,MATCH(G2:H2,$B$2:$D$2,0),FALSE))

arrayformula gsheets

Note: No need to highlight the output range before entering the formula. The array formulas in Google Sheets are dynamic.