Table of Contents
Excel’s VLOOKUP function can be used to look up and return multiple columns of data by nesting multiple VLOOKUP functions together. The first VLOOKUP returns the lookup value, and each subsequent VLOOKUP functions searches for that value in a different column and returns the corresponding value. This allows the user to create a single formula to return multiple columns of data from a single lookup.
You can use the following syntax with the VLOOKUP function in Excel to look up a value in a range and return the corresponding values from multiple columns:
=VLOOKUP(G2,A2:E11,{3,4,5},FALSE)
This particular formula looks in the range A2:E11 and returns the corresponding values in columns 3, 4, and 5 where the value in column A is equal to G2.
Note: The FALSE argument tells Excel to look for exact matches instead of approximate matches.
The following example shows how to use this syntax in practice.
Example: Use VLOOKUP to Return Multiple Columns
Suppose we have the following dataset in Excel that shows information about various basketball teams:
We can use the following formula with VLOOKUP to look up the team “Pacers” in column A and return the corresponding values for points, assists, and steals:
=VLOOKUP(G2,A2:E11,{3,4,5},FALSE)
Note: After typing this formula, make sure you press Ctrl+ Shift + Enter.
The following screenshot shows how to use this formula in practice:
The VLOOKUP function returns the values in the points, assists, and steals columns for the row where the team contains “Pacers” in the name.
Note: You can find the complete documentation for the VLOOKUP function .