In this tutorial, you will learn how to vlookup to return multiple columns in Google Sheets.
How to VLOOKUP to Return Multiple Columns in Google Sheets
Quick Navigation
The VLOOKUP function is a powerful function in Google Sheets that allows you to look up information from one table and return it to another. It is commonly used when your spreadsheet has two tables of data that are related to each other in some way.
The VLOOKUP function allows you to return a single column of data, but it can also be used to return multiple columns. This can be helpful if you need to quickly lookup information from one table and return multiple values, such as a name and an email address.
This is possible because of the ARRAYFORMULA function in Google Sheets. This function allows you to pass an array of values as a parameter and have the function return a value for each element in the array.
In this guide, we will show you how to use the VLOOKUP function and ARRAYFORMULA function to look up multiple columns in Google Sheets.
How to Return Multiple Columns in Google Sheets with VLOOKUP in Google Sheets
Here’s our step-by-step guide on how to return multiple columns in Google Sheets with vlookup in Google Sheets.
Step 1
First, select an empty cell that will hold our formula.
In this example, we want our formula to output the last name, amount, and date of a transaction given a transaction ID.
Step 2
We’ll start our formula by typing the ARRAYFORMULA function.
The ARRAYFORMULA function will allow us to output an array with our VLOOKUP function.
Step 3
Inside the ARRAYFORMULA function, we’ll set up our VLOOKUP function. Type the cell reference to the lookup value to use.
In our example, we want to use our transaction id in cell H2.
Step 4
Next, we’ll add the range of our lookup table.
Step 5
The third parameter of VLOOKUP will indicate what index to return after performing a lookup. If we add an array of numbers, we’ll be able to output multiple values (provided the formula is set up as an array formula).
In our example above, we added the argument {3, 5, 6} since these correspond to the last_name, amount, and transaction_date fields in our lookup table.
Step 6
When we evaluate the function, all three of these values are returned as a single row.
We can then use the AutoFill tool to fill out the rest of the table. Do note, however, that we’ll have to convert our lookup table range into an absolute reference.
Summary
This guide should be everything you need to learn how to vlookup to return multiple columns in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.