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

Here are two simple methods!
Last Updated on February 21, 2024
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

1. Using the helper column
2. 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:

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:

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:

The result:

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

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:

### 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)

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:

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.

#### 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:

### 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:

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

## 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.