Example: OFFSET and MATCH function
The combination of OFFSET and MATCH is a lookup formula just like VLOOKUP or INDEX MATCH.
Especially like INDEX MATCH!
MATCH looks for a value and returns the row number of the found value to the OFFSET function. And the OFFSET function returns the cell content of a corresponding cell in the same row as the found value.
The syntax of the normal OFFSET function looks like this:
=OFFSET(starting cell, rows, columns, [height], [width])
We’re not using the last 2 arguments here, so we’ll use this and then insert the MATCH function later:
=OFFSET(starting cell, rows, columns)
Let’s look up a name and return that person’s salary.
Step 1: Set the starting cell reference
Start writing the OFFSET formula:
Step 2: Replace rows argument with MATCH function
The 2nd argument of the OFFSET function determines how much down/up it should return something from. Instead of writing a value here, use the MATCH function to find it automatically.
Step 3: MATCH lookup value
You use the MATCH function as you normally would. So, start with making a cell reference to the value you want to look for (the lookup value).
Step 4: MATCH lookup array
The lookup array is the column where you want to search for the lookup value.
When lookup for a name, it’s the “Name” column (column A).
Step 5: MATCH type
The last argument of the MATCH function determines which type of match you want.
99% of the time, you want an exact match. So, write:
Step 6: OFFSET function columns argument
The MATCH function found the lookup value and knows which row it is located in. It returns that row number to the OFFSET function.
But the last part of the OFFSET function determines which column, in the same row as the lookup value, to return the cell content from.
The “return column” is located 2 columns to the right of the starting cell (the first argument of the OFFSET function, A1).
Your final OFFSET formula should look like this:
And that returns the salary for Samuel. Yay🎉