This post will guide you how to lookup a value from a range of cells and return match from adjacent cell in Excel 2013/2016/2019/365.  How do I Vlookup a value and return a value in corresponding adjacent cell in Excel.

In this tutorial, we’ll guide you two methods to search for a specific value and retrieve the corresponding information from an adjacent cell. Specifically, we will explain the first method using VLOOKUP function. And another method is using the INDEX function in combination with the MATCH Function.

1. Look up Value and Return Adjacent Cell Value Using VLOOKUP Function

Assuming that you have a list of data in range A1:B8, and you need to vlookup one product name and return the adjacent sale value of Sales column. Then you can use a formula based on the VLOOKUP function. Like this:

=VLOOKUP("excel",A2:B8,2, FALSE)

Type this formula in a blank cell and then press Enter key to apply it.

The adjacent cell value of product  “excel” in Column Product is returned in Cell D1.

The VLOOKUP function searches for a value in the leftmost column of a range and returns a value in the same row from a specified column. At this example, the above formula searches for the value “excel” within the range A2:B8 and returns the value from the second column (column B) in the same row.

2. Look up Value and Return Adjacent Cell Value Based on Index and Match functions

If you want to lookup value and return the value in the next cell of the adjacent cell, and you can use another formula based on the INDEX function and the MATCH function. Like this:

= INDEX(B2:B8,MATCH(“excel”,A2:A8,0)+1)

Type this formula in a blank cell and then press Enter key to apply it.

These two methods provide different ways to look up a value and return an adjacent cell value in Excel. Above all, you can choose the one that suits your specific needs and data structure.

3. Video: Look up Value and Return Adjacent Cell Value

In this video, I will demonstrate how to use the VLOOKUP function, as well as the INDEX and MATCH functions, to look up a value in Excel and return the corresponding value in an adjacent cell.

4. Related Functions

• Excel VLOOKUP function
  The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position. The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
• Excel INDEX function
  The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function. The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel MATCH function
  The Excel MATCH function search a value in an array and returns the position of that item. The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….