How to use VLOOKUP to return one value from multiple columns - 2 simple methods

You can trust PC Guide: Our team of experts use a combination of independent consumer research, in-depth testing where appropriate - which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

If you want to learn how to use VLOOKUP to return one value from multiple columns, we’ve got your back.

VLOOKUP’s syntax only returns one value by looking up data from one column. However, if you have data where you want to look through multiple columns, the simple VLOOKUP function will not work. Lucky for you, we have consolidated two alternative ways to use VLOOKUP to return one value by looking through multiple columns.

So, without wasting any time, let’s dive in!

Using VLOOKUP to return one value from multiple columns

Scenario on hand: We have a fictional dataset of books with their release year, author name, and first-year sales.

What we want to accomplish: Explore the ways to use VLOOKUP to fetch one return by looking through multiple columns

Using the helper column
Using two VLOOKUP formula

We will also explore how we can use VLOOKUP to return two values.

Method 1: Using the helper column

Helper columns consolidate data from two or more columns for the VLOOKUP function to search through multiple data types.

In our case, we need VLOOKUP to fetch the author’s name based on the book name and the release year.

So, we added a helper column on the table’s left like this:

A spreadsheet in Excel with multiple columns highlighted.

We use the concatenate formula to consolidate the release year and book name in the helper column.

Here’s the syntax of the concatenate formula:

=CONCATENATE({Cell address 1}, {Cell address 2})

Here’s how that looked for our dataset:

=CONCATENATE(B6, C6)

Now, using the Fill handle tool, expand the formula to the entire column like this:

A screenshot of an Excel spreadsheet showcasing VLOOKUP and Multiple Columns.

The result:

A screenshot of an Excel spreadsheet with multiple columns.

In a separate space, add the two lookup values like this:

A screenshot of a table in an Excel spreadsheet with multiple columns.

In the lookup cell, add this formula:

=VLOOKUP({Cell address of lookup value 1}&{Cell address of lookup value 2}, Table array, Column index number, FALSE)

For our dataset, this formula turns to:

=VLOOKUP(H5&H6, A6:E14, 4, FALSE)

Here’s what that returns:

Learn how to create a spreadsheet in Excel with multiple columns and use the VLOOKUP function.

Method 2: Using two VLOOKUP formula

This method works in multiple ways. In this tutorial, we will explore all of these ways.

Data in a single table

Since our data is in a single table, here’s how this method will work.

Enter this formula in a cell:

=VLOOKUP(VLOOKUP({Lookup value from leftmost column},{Table array with first two columns},2,FALSE),{Table array with 2nd column as first column}, {Column index number}, FALSE)

For our data, the formula turns out like this:

=VLOOKUP(VLOOKUP(H5,B6:C$14,2,FALSE),C$6:E$14,2,FALSE)

Learn how to create a spreadsheet in Excel, including utilizing VLOOKUP and managing multiple columns effectively.

The above method worked because our dataset only had one book released in 2017. A better way to use this formula is on a dataset where every entry is assigned a code.

Data in two separate tables

For this method, let’s introduce code numbers for each entry and create separate tables:

A screenshot of an excel spreadsheet with multiple columns filled with numbers.

Now, let’s add a VLOOKUP formula that includes both tables and a single lookup value (BV-2017)

=VLOOKUP(VLOOKUP(J5,B6:C14,2, FALSE), E6:G14, 2, FALSE)

Here, the lookup return of BV-2017 from Table 1 becomes the lookup value for Table 2 and returns the author name from column 2.

A screenshot of a Microsoft Excel spreadsheet with multiple columns.

Data on two separate sheets

You can also use this formula if your tables are on separate sheets. The only adjustment you will need to make is adding the sheet name in front of every value like this:

=VLOOKUP(VLOOKUP(G5,Codes!B6:C14, 2, FALSE), ‘Author and Sales’!B6:D14, 2, FALSE)

This is the return we get when we input this formula into our Excel workbook:

Learn how to create a spreadsheet in Excel with multiple columns for efficient data organization.

Using VLOOKUP to return two values

While the VLOOKUP syntax only asks for one column index number, we can modify it to return two values.

Here’s how we modified the syntax:

=VLOOKUP({Cell address of lookup value},Table array,{Column index numbers separated by a comma},FALSE)

Here’s how this is looking for our dataset:

=VLOOKUP(G6,B6:E14, {2,3}, FALSE)

Select both return cells like this when entering the formula and press Ctrl+Shift+Enter instead of only pressing Enter:

A screenshot of an Excel spreadsheet with an arrow pointing to a table displaying data in multiple columns.

Pressing Ctrl+Shift+Enter converts that part of the sheet into an array. We get both returns with one single formula:

A screenshot of an Excel table displaying multiple columns.

Wrapping up

Using these 2 techniques, you can use VLOOKUP to return one value from multiple columns. We also explored how you can use VLOOKUP to return two values. These techniques can help you out greatly when dealing with a large data set.

Learn more about VLOOKUP and how it works through these helpful guides:

How to use VLOOKUP to return one value from multiple columns – 2 simple methods

Table of Contents