Table of Contents
VLOOKUP is an Excel function that can be used to look up values from a table of data. To return a value in the next cell, you can use the VLOOKUP function with the col_index_num parameter set to 0 (zero). This will cause the function to return the value that is to the immediate right of the lookup value. You can then use this value in the next cell.
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.