Often you may want to use the VLOOKUP function in Excel to look up a specific value in a range and return the corresponding in the next cell in a different range.
The easiest way to do this is by using the INDEX and MATCH functions as follows:
=INDEX(B2:B11,MATCH("Lakers",A2:A11,0)+1)
This particular formula looks up the value “Lakers” in the range A2:A11, then returns the corresponding value in the range B2:B11 that is one cell below it.
The following example shows how to use this formula in practice.
Example: Use VLOOKUP to Return Value in Next Cell in Excel
Suppose we the following dataset in Excel that shows the points scored by basketball players on various teams:
Suppose we would like to look up “Lakers” in the Team column and then return the value from the Points column that is one below the the points value for the Lakers.
To do so, we can type the following formula into cell D2:
=INDEX(B2:B11,MATCH("Lakers",A2:A11,0)+1)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 23, which is the value in the Points column that is one below the points value for the Lakers.
Note that if you’d like to return the value from the cell that is one above the points value for the Lakers, you could subtract one from the end of the formula instead:
=INDEX(B2:B11,MATCH("Lakers",A2:A11,0)-1)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 17, which is the value in the Points column that is one above the points value for the Lakers.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: Use VLOOKUP to Return Blank Instead of #N/A
Excel: Use VLOOKUP to Find Value That Falls Between Range
Excel: Use VLOOKUP to Return Last Matching Value
