You can use the following syntax in Excel to use the VLOOKUP function to look up a specific value in a range and return the entire row that corresponds to the value:
=VLOOKUP(A14,$A$2:$D$11,{1,2,3,4},FALSE)
This particular example looks up the value in cell A14 in the range A2:D11 and then returns the entire row that corresponds to the value.
Note: By using {1, 2, 3, 4} in the formula we are able to return the values in columns 1, 2, 3 and 4.
The following example shows how to use this syntax in practice.
Example: Use VLOOKUP to Return Entire Row in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we would like to look up the team name “Mavs” in the dataset and return the entire row that corresponds to this team name.
We can type the following formula into cell A16 to do so:
=VLOOKUP(A14,$A$2:$D$11,{1,2,3,4},FALSE)
The following screenshot shows how to use this formula in practice:
Notice that the formula returns the entire row of values that corresponds to the “Mavs” team name.
If we change the value of the team in cell A14, the formula will automatically update to return the entire row of values that corresponds to the new team name.
For example, suppose we change the team name to “Nets” in cell A14:
Notice that the formula automatically updates to return the entire row of values that corresponds to the “Nets” team name.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Use VLOOKUP to Return Multiple Values Horizontally
Excel: Use VLOOKUP to Return Blank Instead of Zero
Excel: How to Use VLOOKUP by Date
