How can I use VLOOKUP to return the value in the next cell in Excel?

By /

The VLOOKUP function in Excel allows users to search for a specific value in a table and return a corresponding value from a different column. By using the “FALSE” argument, this function can also be used to return the exact match of the lookup value in the next cell. This can be useful for organizing and analyzing data in a spreadsheet, as it eliminates the need for manual searching and copying of data. Additionally, VLOOKUP can be combined with other functions, such as IF or ISERROR, to further manipulate the returned value.

Excel: Use VLOOKUP to Return Value in 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:

Excel VLOOKUP return next cell

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:

x

Related terms:

  1. How to Use VLOOKUP to Return Value in Next Cell in Excel?
  2. Excel: Use VLOOKUP to Return Multiple Values in One Cell
  3. How do I use VLOOKUP in Google Sheets to return the last matching value?
  4. How can I use VLOOKUP in Google Sheets to return the minimum value from a data set?
  5. How can I use VLOOKUP in Google Sheets to return the maximum value?
  6. Excel Formula: If Blank Then Return Value of Another Cell
  7. Excel: If Cell Contains Text then Return Value
  8. How can I use VLOOKUP in Google Sheets to return the last matching value?
  9. How can VLOOKUP be used in Google Sheets to return the minimum value?
  10. How can VLOOKUP be used to return the maximum value in Google Sheets?