How to Return Multiple Columns with VLOOKUP in Excel 2016

Excel 2016 can return several columns from one VLOOKUP, but it does not spill the result automatically. Select every destination cell before entering the formula and confirm it as a legacy array formula with Ctrl+Shift+Enter.

Why Excel 2016 Returns Only One VLOOKUP Value

A normal VLOOKUP uses one column index. An index array such as {2,3,4} can return three fields, but Excel 2016 must know the complete output range. Three index numbers require three selected result cells, and the lookup column must be the first column of the table array.

Return Multiple VLOOKUP Columns Step by Step

1. Select the adjacent cells that should receive the returned fields.
2. Enter =VLOOKUP($A2,$H$2:$K$100,{2,3,4},FALSE), adjusting the value and table range.
3. Press Ctrl+Shift+Enter. Do not type the outer curly braces yourself.
4. Compare every result with a known row before copying the full array range down.

Use Separate VLOOKUP Formulas as an Alternative

For easier maintenance, use one VLOOKUP in each result column and change the index from 2 to 3 to 4. You can also calculate the index with COLUMNS($B:B)+1 and fill right. This is a different mechanism and avoids editing a multi-cell legacy array.

Verify References and Duplicate Lookup Keys

Confirm exact-match mode, absolute references, blank inputs, and duplicate keys. VLOOKUP returns the first match, so duplicate keys need an additional unique field if they represent different records.

Return Multiple Lookup Fields with WPS Spreadsheets

Use Word, Excel, and PPT for FREE

Free Download

WPS Office provides WPS Spreadsheets as a free, lightweight option for common XLSX work. Open a duplicate workbook, enter one exact-match VLOOKUP per result column, lock the lookup and table references, fill across, and test known records before saving.

WPS AI can assist with formula explanations and data analysis, but verify macros, external connections, and organization-managed add-ins before replacing a critical Excel workflow.

Return Multiple Columns with VLOOKUP in Excel 2016 FAQs

Why does Enter return only one value in Excel 2016?

Excel 2016 does not spill the index array. Select the entire result range first and confirm with Ctrl+Shift+Enter.

Should I type curly braces around the formula?

No. Excel adds the outer braces after it accepts Ctrl+Shift+Enter.

Can VLOOKUP return nonadjacent columns?

Yes. Use an index array such as {2,4,6} and select the same number of output cells.

What is the easiest non-array alternative?

Use a separate VLOOKUP in each output column and change or calculate its column index.