How to Vlookup And Return Multiple Columns in Google Sheets (In 3 Easy Steps)

Are you working on a task that needs you to find and bring together information from different columns in Google Sheets, but you’re not quite sure how to do it? Great news! This guide is designed to help you understand exactly how to Vlookup And Return Multiple Columns in Google Sheets.

Sometimes, when using Google Sheets, you might need to gather different pieces of data that match a particular keyword. After that, you may want to use this gathered data to feed into another formula.

However, it can be a bit tricky when you want to use this information in another formula, like drawing a line that shows a trend over time (known as a sparkline).

So, how can you pull this off? Stick with me as we explore a simple formula that allows you to search and bring back information from multiple columns in Google Sheets.

The Scenario/Use Case Of Returning Multiple Columns in Google Sheets Using Vlookup

To better understand this concept, let’s imagine a scenario. Suppose you are a school teacher with a spreadsheet filled with student data. Each row has a student’s name, and each column has details about that student, like their grades in different subjects, attendance records, and so on.

Now, you need to prepare a report showing the Math, Science, and English grades for a specific student, say “John Doe”. Instead of manually looking for each grade, wouldn’t it be great if you could just type “John Doe” and have Google Sheets automatically fetch his grades for you?

This is exactly the kind of task our formula will help you do. It’s like teaching Google Sheets how to look up multiple columns and gather all the information you need at once.

Keep reading to learn how to do it, perhaps with a more clear example.

How To VLOOKUP And Return Multiple Columns in Google Sheets (Explained with Examples)

If you want to search for information and get results from multiple columns in Google Sheets, you’ll need to use something called array literals, which are represented as {}. These curly braces help you specify the columns you want to gather data from. Not only that, but they also change your formula into an Array Formula, telling Google Sheets that you’re working with a whole range of outputs, not just one piece of data.

For those wondering what exact formula you can use to do this, here’s a handy explanation.

Imagine you have a group of data in the range from cell A1 to G9, and you’re looking for a value located in cell A14. The formula to search and gather data from multiple columns in Google Sheets would look something like this:

=ArrayFormula(VLOOKUP($A$14,$A$1:$G$9,{2,3,6,7},FALSE))

We know this may look a bit complicated at first glance, but don’t worry. As we dive into the practical part of this tutorial, you’ll see how straightforward it is to use this formula to look up and return vital data from multiple columns in Google Sheets.

---

Ready to jump into the practical guide? Well, here is the data sample we will use for today’s example. 

Let’s assume we want to VLOOKUP on the Transaction ID EM1089 in column A and return the corresponding values for Payment Date, Product, Revenue, and Sales Channel. We can do that using the formula we will show you shortly. 

But before we delve into the step-by-step guide, keep in mind that we first need to perform a regular VLOOKUP but in place of a single-column index. What we simply want to do is put an array of columns we would like to return, surrounded by curly brackets. Here is exactly what we mean. 

{2,3,6,7}

Adding this to our formula will return values for the 2nd, 3rd, 6th and 7th columns, which house the data we want to return. 

After entering the VLOOKUP formula in the search bar, you can simply press Ctrl + Shift + Enter if you’re using a Windows PC or CMD + Shift + Enter if you’re using MAC. Performing the following formula converts your VLOOKUP formula into an Array Formula. 

All of the explanation detailed above is to simply convert this formula: 

=VLOOKUP($A$14,$A$1:$G$9,{2,3,6,7},FALSE)

Which wouldn’t give us the desired output, to this one below:

=ArrayFormula(VLOOKUP($A$14,$A$1:$G$9,{2,3,6,7},FALSE))

Which gives us exactly what we want. 

Now that we have explained quite extensively, here is how to use the formula to VLOOKUP the transaction ID EM1089 in column A and return the corresponding values for Payment Date, Product, Revenue and Sales Channel. 

Step 1: Choose the cell where you want the result generated

Since you know exactly what you’re looking for (Search term), you need to choose the cell where you want the result generated. For our example, we will use cell B14. We have updated our spreadsheet to show where we would like our output to be generated. 

Step 2: Type in the ArrayFormula 

Having chosen where we would like our output to be generated, we need to go ahead to type in our ArrayFormula. So head over to the formula bar and type in the following formula: 

=ArrayFormula(VLOOKUP($A$14,$A$1:$G$9,{2,3,6,7},FALSE))

Step 3: Hit enter

After executing the steps detailed above, simply hit the Enter button on your keyboard, and Google Sheets will automatically generate the output in your preferred cell. Here is what ours looks like. 

Note: If you look at the screenshot above, you’ll notice that we have a strange number in cell B14 underneath the “Payment date column.” That’s just a formatting issue. And since we don’t want that,  we need to format that column accordingly. To do that, head over to the Format menu, select the option for Numbers and choose Date.

If you did everything exactly as we showed you above, you’ll notice some obvious changes, here is what ours looks like: 

What’s cool about using the formula we showed you is that it makes the entire process seamless. And guess what? If we were to change the search term to something else, say EM1020, Google Sheets will automatically change the output without having us repeat the entire process from scratch. The video below provides better insight. 

Note: Using the Array Formula we showed you, we were able to search for a single search term and return values from four different columns. Feel free to select whichever columns you want. Just make sure to update your formula accordingly. 

Final Thoughts 

If you have found it challenging to VLOOKUP and return multiple columns in Google Sheets in the past, we are sure today’s guide has made it easy for you. 

We started off today’s guide by giving you a bit of background on performing VLOOKUP for multiple columns in Google Sheets, after which we highlighted the one formula you’d need to VLOOKUP and return multiple columns in Google Sheets. 

We also went above and beyond to provide a detailed step-by-step guide on how to VLOOKUP and return multiple columns in Google Sheets. Our tutorial features multiple screenshots and video resources to simplify the process. 

In case you encounter any issues while executing the steps we showed you today, feel free to consult our screenshots and video resources for better insight. 

We hope today’s guide has been helpful. 

For more awesome Google Sheets tutorials, feel free to check our blog, as we have tons of Google Sheets tutorials that will make your job easy.