Have you ever found yourself in a situation where you wished the VLOOKUP function could return multiple columns of data? Such a feature can be incredibly useful when you want to retrieve multiple values that match a search term, to use as input in another formula (e.g., a line chart).
Sure, you could set up multiple VLOOKUP formulas, but that wouldn’t work if you want to use the data range as input for another formula, like a line chart, for example.
However, it is possible to achieve all this with just a single formula!
Getting Multiple Columns with the VLOOKUP Function
We use array literals {} to indicate which columns we want to retrieve, and then we convert the formula into an array formula to let Google Sheets know that we are working with a data range rather than a single value.
What is the formula for getting multiple values with the VLOOKUP function?
Let’s assume I have a data table in the range A1:G9 and my search value in A14, as shown in the image above. The lookup formula is as follows:
=VLOOKUP(A14, A1:G9, {2,3,6,7}, false)Can I see an example spreadsheet?
Of course, here’s one:
How does this formula work?
Imagine this is your raw data table, and you want to look up an ID and retrieve values from multiple columns:
Essentially, we’re performing a regular VLOOKUP, but instead of a single column index, we’re inputting an array of columns we want to retrieve, surrounded by curly brackets, like this:
{2,3,6,7}which returns columns 2, 3, 6, and 7.
Next, you need to press Ctrl + Shift + Enter (on PC) or Cmd + Shift + Enter (on Mac) once you’ve entered the VLOOKUP formula to transform it into an array formula (you can also simply type the word “ArrayFormula”).
So, you go from this formula:
=VLOOKUP(A14, A1:G9, 2, false)which won’t work, to this:
={VLOOKUP(A14, A1:G9, {2,3,6,7}, false)}which works and returns an array (i.e., multiple values). It’s important that the output cells (4 in this case) are all empty for the formula to work.
Here’s what happens:
We look up a single search term and retrieve values from four columns that are returned in four adjacent cells. You can select the columns you want and change the order as well.
At bolamarketing.com, we love sharing our juiciest secrets. To discover more tips and tricks to enhance your productivity and experience with Google Sheets, feel free to visit our site Crawlan.com. Happy Sheets-ing!
