Often you may want to look up values in multiple columns in Excel and return a corresponding value in another column.
For example, you may want to look up the team “Spurs” and the position “Center” in the following dataset and return the corresponding value from the points column:
The following step-by-step example shows exactly how to do so using the VLOOKUP function.
Step 1: Create the Dataset
First, let’s create the following dataset that contains information about various basketball players:
Step 2: Create a Helper Column
Next, we need to create a helper column in order for the VLOOKUP function to work correctly.
To do so, type the following formula into cell A2:
=B2&C2
Then click and drag this formula down to each remaining cell in column A:
Step 3: Perform VLOOKUP from Multiple Columns and Only Return One
Lastly, we can type the following formula into cell G3 to look up “Spurs” in the team column and “Center” in the position column and return the corresponding value in the points column:
=VLOOKUP(G1&G2, A1:D10, 4, FALSE)
The following screenshot shows how to use this formula in practice:
The formula correctly returns a value of 35, which is the points value that corresponds to the player on the “Spurs” team with “Center” as their position.
Note that in the formula we used G1&G2 to concatenate the values in cells G1 and G2.
Thus, we effectively looked up the value SpursCenter in column A and returned the corresponding value in the fourth column of the range.
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
