Excel VLOOKUP Multiple Columns in 5 Easy Steps (+ Example Workbook)

In this Excel VLOOKUP Multiple Columns Tutorial, you learn how to:

Carry out a VLOOKUP; and
Return values from multiple columns.

This Excel VLOOKUP Multiple Columns Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.

Table of Contents

This Excel VLOOKUP Multiple Columns Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

Excel VLOOKUP Tutorial (under development): Click here to open.
Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
Excel VLOOKUP Compare 2 Columns and Find Matches in 4 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Row Values (in Same Column) in 4 Easy Steps: Click here to open.
Excel VLOOKUP Sum Multiple Columns (Values) in 6 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Column Values (with XLOOKUP) in 4 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Rows and Columns in 3 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values with the INDEX Function in 7 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Sheets in 10 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Sheets in Different Workbook in 10 Easy Steps: Click here to open.
Excel VLOOKUP Sheet in Multiple Different Workbooks in 10 Easy Steps: Click here to open.

My Excel XLOOKUP Tutorial (click here to open) may help you:

Better understand and implement the contents below.
Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

Excel Macro Tutorial for Beginners: Click here to open.
Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
350 + Excel Keyboard Shortcuts And Hotkeys: Click here to open.

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

Excel essentials and must-know skills.
Advanced Excel Formulas.
Excel Tables.
Pivot Tables.
Dashboards.
Power Pivot.
Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open.
Books at the Power Spreadsheets Library: Click here to open.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

The VLOOKUP Multiple Columns Formula Template/Structure

The following is the VLOOKUP multiple columns formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
=VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToReturn,TrueOrFalse)

This is an array formula. If you're working with Excel 2019 or earlier, enter this formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
{=VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToReturn,TrueOrFalse)}

The Example Before VLOOKUP Multiple Columns

The example worksheet has 2 tables/sections with the following characteristics:

(1) Table 1 (Cells A8 to H28).

The table:

With the data.
Where you search with the VLOOKUP multiple columns example formulas.

Main characteristics:

8 columns (Salesperson, Date, Customer, Product, City, Units, Unit Price, Total Sales).
1 header row (row 8).
20 entries (rows 9 to 28).

(2) Table 2 (Cells J8 to R11).

The table:

Where you set up the VLOOKUP multiple columns example formulas.
Display the results.

This table has the following 4 rows:

Column: Cells K8 to R8 store numbers. These numbers match the column numbers (see cells A6 to H6) of Table 1 (the table VLOOKUP works with).
Header: The values in cells K9 to R9 match Table 1's headers (cells A8 to H8).
Value:
- Cell K10 contains the lookup value.
- Cells L10 to R10:
  - Are currently empty.
  - Will store the VLOOKUP multiple columns example formulas.
Formula: Cell L11:
- Currently displays the #N/A error.
- Will display the VLOOKUP multiple columns example formula I enter in cell L10.

VLOOKUP multiple columns worksheet before formulas

Step 1: Specify the Lookup Value

Specify the first argument of the VLOOKUP function: lookup_value.

lookup_value is the value you search for in the first/leftmost column of the table you work with. The VLOOKUP function is case insensitive.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
=VLOOKUP(LookupValue,

Step 1 Example

The lookup value for the VLOOKUP multiple columns example formulas is stored in cell K10 ($K$10, when using absolute references).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
=VLOOKUP($K$10,

Lookup value for VLOOKUP multiple columns

Step 2: Specify the Cell Range You Look In

Specify the second argument of the VLOOKUP function: table_array.

The table_array argument is the cell range where you look in (the table with the applicable data).

The first/leftmost column of the table (you specify as table_array) must contain the lookup_value (you specified in step #1).
As a general rule, if the first/leftmost column in the table contains duplicate values (and you look up one of those duplicate values), the VLOOKUP function works with the first entry matching the lookup value.
The cell range you specify as table_array argument must (also) contain the column with the value to return.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
=VLOOKUP(LookupValue,CellRangeWhereYouLookIn,

Step 2 Example

The cell range the VLOOKUP multiple columns example formulas look in is cells A9 to H28 ($A$9:$H$28, when using absolute references).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
=VLOOKUP($K$10,$A$9:$H$28,

VLOOKUP return multiple columns table you look in

Step 3: Create an Array with the Numbers of the Multiple Columns with the Values to Return

Specify the third argument of the VLOOKUP function: col_index_num.

The col_index_num argument is the number of the column (in the table you specified in step #2) with the value to return.

The first column in the table is column 1.
The second column in the table is column 2.
…
The nth column in the table is column n.

When creating a VLOOKUP multiple columns formula, you can specify the col_index_num argument in several ways, including the following 3:

Using cell references (to the cells containing the applicable column numbers).
Working with a function, such as SEQUENCE. The SEQUENCE function:
- Is available in Excel 2021 and later (including Excel 365).
- Allows you to generate a list of sequential numbers (in an array).
Hardcoding the multiple column numbers.

If you choose to hardcode the multiple column numbers, specify the col_index_num argument as follows:

Specify the multiple column numbers as a comma-delimited list. In other words: Use commas (,) to separate the multiple column numbers (NumberOfColumnWithValueToReturn1,NumberOfColumnWithValueToReturn2,…,NumberOfColumnWithValueToReturn#).
Wrap the comma-delimited list of multiple column numbers in curly braces ({NumberOfColumnWithValueToReturn1,NumberOfColumnWithValueToReturn2,…,NumberOfColumnWithValueToReturn#}).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
=VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToReturn,

Step 3 Example

The numbers of the columns (in the table I specified in step #2) with the values to return (by the VLOOKUP multiple columns example formulas) are stored in cells L8 to R8 ($L$8:$R$8, when using absolute references). These numbers match the column numbers (see cells A6 to H6) of the table I specified in step #2.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
=VLOOKUP($K$10,$A$9:$H$28,$L$8:$R$8,

Multiple columns with values to return with VLOOKUP

Step 4: Specify Whether You Want an Approximate or Exact Match

Specify the fourth argument of the VLOOKUP function: range_lookup.

The range_lookup argument specifies whether you want an approximate or exact match, as follows:

TRUE: The VLOOKUP function returns an approximate match. An approximate match lookup is (also) known as a range lookup (you lookup a value inside a range of values).
FALSE: The VLOOKUP function returns an exact match.

If you omit the range_lookup argument, the VLOOKUP function:

Returns an approximate match; but
(Usually) Returns an exact match if:
- An exact match exists; and
- The first/leftmost column in the table (you specified in step #2) is sorted in ascending order.

If you want an approximate match (set the range_lookup argument to TRUE, or omit the range_lookup argument):

The first/leftmost column (in the table you specified in step #2) must be sorted in ascending order.
If the lookup value (you specified in step #1) is:
- Smaller than the first value in the first/leftmost column of the table (you specified in step #2), the VLOOKUP function returns the #N/A error.
- Larger than the last value in the first/leftmost column of the table (you specified in step #2), the VLOOKUP function works with that last value in the column.

If you want an exact match (set the range_lookup argument to FALSE) and the VLOOKUP function fails to find an exact match for the lookup value (you specified in step #1) in the table (you specified in step #2), the VLOOKUP function returns the #N/A error.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
=VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToReturn,TrueOrFalse)

Step 4 Example

The VLOOKUP multiple columns example formulas return an exact match. I set the range_lookup argument to FALSE.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
=VLOOKUP($K$10,$A$9:$H$28,$L$8:$R$8,FALSE)

Excel VLOOKUP multiple columns formula example

Step 5: Enter the Formula as an Array Formula

The VLOOKUP multiple columns formula template/structure you learned in this Tutorial is an array formula.

If you're working with Excel 2019 or earlier, enter this VLOOKUP multiple columns formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
{=VLOOKUP(LookupValue,CellRangeWhereYouLookIn,NumbersOfColumnsWithValuesToReturn,TrueOrFalse)}

Step 5 Example

The image below displays the VLOOKUP multiple columns example formulas wrapped in curly braces ({}) if I press “Ctrl + Shift + Enter” to enter them. The results are the same as those displayed in step #4.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-columns/
{=VLOOKUP($K$10,$A$9:$H$28,$L$8:$R$8,FALSE)}

Excel VLOOKUP multiple columns array formula

Download the VLOOKUP Multiple Columns Example Workbook

This Excel VLOOKUP Multiple Columns Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.