You can use the following syntax with the OFFSET and MATCH functions in Excel to look up a specific value in a range and return the corresponding value in a different column:
=OFFSET(B1, MATCH(F1, A1:A11, 0)-1, 0, 1, 1)
This particular formula will look up the value in cell F1 within the range A1:A11 and then return the corresponding value from column B.
The following example shows how to use this formula in practice.
Example: How to Use OFFSET and MATCH in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
Now suppose we would like to look up the team name “Thunder” in the dataset and return their points value.
To do so, we can type the following formula into cell F2:
=OFFSET(B1, MATCH(F1, A1:A11, 0)-1, 0, 1, 1)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 25, which is the points value that corresponds to the “Thunder” team name.
Note that if you’d like to return the value from a different column, you simply need to change B1 to a different column.
For example, you can instead use C1 to return the value in the assists column that corresponds to the “Thunder” team name:
=OFFSET(C1, MATCH(F1, A1:A11, 0)-1, 0, 1, 1)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 10, which is the assists value that corresponds to the “Thunder” team name.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use INDEX and MATCH to Return Multiple Values Vertically
Excel: How to Use VLOOKUP to Return Multiple Columns
Excel: How to Use VLOOKUP to Return All Matches
