How To Return Multiple Columns with VLOOKUP and Data Validation

The VLOOKUP function is unquestionably in my top 5 most used functions. I love the simplicity of this function, but the true beauty is the power it possesses; it’s genuinely remarkable; let’s take a look and see why.

You could spend forever trying to locate something within a spreadsheet if you had to do it without the help of a VLOOKUP function. Thankfully, you don’t have to once you know how to use this renowned function, but one of its hidden treasures remains a mystery to most people.

Typically, most people will use the VLOOKUP function to look up a search key in a range of data and return a single value from a defined column within that range.

Still, it’s possible to return more than one value across multiple columns by using what’s known as an array literal.

It might sound intimidating, but it only requires a slight modification to the usual VLOOKUP function to unlock its ability. It's also possible with the modern XLOOKUP function, so make sure you check this out.

Let’s dive in a take a look at an example using the table of data below.

Suppose you want to isolate just one row from a table, but you need to see all the associated columns. This is achievable this with one formula.

This example below is what we want to see once we’ve finished. The top table is showing an entire row from the lower table.

In the first column of the first table, I’ve set up a data validation dropdown to show all the options listed in the first column of the second table below it; this will allow you to dynamically change the results in the first table.

VLOOKUP

Using a normal VLOOKUP function we would simply return a single value like this below: 5,641.

=VLOOKUP(B3,$B$6:$G$14,2,FALSE)

Search Key

The lookup search key, in this case, is the channel. This is the B3 cell with a value of ‘Paid Search’. We’re asking the function to find this search key in the range.

=VLOOKUP(B3,$B$6:$G$14,2,FALSE)

Range

The range to search is the entire second table $B$6:$G$14.

The search key must be located in the first column of the range; otherwise, you’ll get an #N/A. I’ve locked this range, which is why you can see the ‘$’ symbol wrapped around the range, but this is not necessary.

Old habits die hard, but to be honest, this is a good habit to get into, especially with VLOOKUP.

=VLOOKUP(B3,$B$6:$G$14,2,FALSE)

Index

The next value in the function is the index - in other words, what column do we want to get the answer from in the range once we find the search key. In this case, we’ve chosen column 2 (Users).

=VLOOKUP(B3,$B$6:$G$14,2,FALSE)

Is Sorted

Lastly, the FALSE statement defines if the range is sorted or not. Generally speaking, you’ll want to use the FALSE clause here. We’ll cover the is_sorted = TRUE another day.

=VLOOKUP(B3,$B$6:$G$14,2,FALSE)

So far, so good, right?

To return more than one column, we just need to change two things.

Array Literal

We need to create an array literal in place of the index so that we can construct the output for multiple columns. Our table has six columns, and we want to return column 2 through to column 6.

Step 1

First we need to list the column numbers in curly braces to create this array literal, like this: {2,3,4,5,6} they need to be separated by commas. We don’t need to return the first column as we have it already with our data validation dropdown menu.

=VLOOKUP(B3,$B$6:$G$14,{2,3,4,5,6},FALSE)

Step 2

Then we need to convert the function into an array formula to return more than one value in our output. You do this by clicking Cmd+Shift+Enter to add ArrayFormula at the beginning of the formula.

The final formula looks like this:

=ArrayFormula(VLOOKUP(B3,$B$6:$G$14,{2,3,4,5,6},FALSE))

There you have it; we retrieved 5 columns of results using one formula.

As an added bonus, you can see how easy it is to quickly isolate a search key using the data validation dropdown; pretty nifty, hey!

You would most likely want to add the summary table on another sheet with the main dataset being on a completely different sheet, particularly if the main dataset contained lots of rows.

This keeps things nice and clean and allows you to display only the information you want to see.

Have a go yourself with this VLOOKUP Function in Google Sheet