Have you ever wished the VLOOKUP function could return multiple columns?
For example, maybe you want to return several values that match a search term so that you can use a range of data as an input for another formula (e.g. a sparkline).
Of course, you could set up multiple vlookup formulas, but this wouldn’t work if you want to pass the data range into another formula, like the sparkline for example.
However, you can easily achieve it with just one formula.
Vlookup return multiple columns
What’s the formula to have Vlookup return multiple values?
Assuming I have a data table in range A1:G9 and my search value in A14, as shown in the image above, then the lookup formula is as follows:
Can I see an example worksheet?
How does this formula work?
Imagine this is your raw data table and you want to search for an ID and return values from multiple columns:
Essentially, we perform a regular VLOOKUP but in place of single column index, we put an array of columns we want to return, surrounded by curly brackets, like so:
which returns the 2nd, 3rd, 6th, and 7th columns.
Then we must press Ctrl + Shift + Enter (on PC) or Cmd + Shift + Enter (on Mac) once we’ve entered the VLOOKUP to turn it into an array formula. (You can also just type in the
So we go from this formula:
which will not work, to this one:
which does work and will output 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.
This is what’s happening:
We search for a single search term and return values from four columns which are output into for adjacent cells. You can select whichever columns you wish as well as changing the order.