OFFSET MATCH MATCH is the final lookup combination I’ll cover among the lookup formula options you have available to you in Excel. Of all the different lookup options you have to do a two-way lookup, INDEX MATCH MATCH is still probably your best bet and the approach I would generally recommend. However, OFFSET MATCH MATCH is a viable alternative. In some ways, using the OFFSET function to find a lookup value is more intuitive because it is very similar to moving a piece around a chess board. Additionally, some people are more familiar with the OFFSET function than they are with INDEX; in that situation, it may make sense to go with OFFSET MATCH MATCH as your lookup formula.
Objective and When to Use
There’s only one simple requirement for using OFFSET MATCH MATCH: the need to perform a matrix style lookup. To put it in simple terms:
The data table you use must have lookup values on both the left hand side as well as the top header row
See the table below for a simple example, with lookup values for country on the left hand side and lookup values for year in the top header row.
Now, when comparing OFFSET MATCH MATCH to INDEX MATCH MATCH, the only real difference is the foundational formula.
INDEX requires you to:
- Define a particular reference array
- Then Excel asks you to tell it where to move within that defined space
In contrast, OFFSET asks you to:
- Designate a starting point
- Then Excel asks you to tell it how to move away from that starting point
They’ll both essentially end up with the same result. OFFSET MATCH MATCH just provides you a slightly different way of getting there. As the post picture suggests, using OFFSET MATCH MATCH is like directing a chess piece a specified number of steps away from a particular space on the board.
= OFFSET ( MATCH ( lookup_value, lookup_array, 0 ), MATCH ( lookup_value, lookup_array, 0 ) )
The overall syntax for the OFFSET MATCH MATCH formula combination is fairly intuitive. You have OFFSET as your primary formula with two MATCH formulas designating the vertical and horizontal offset. We’ll go into detail with both formulas below.
The OFFSET Formula
= OFFSET ( reference, rows, cols, [height], [width] )
The OFFSET formula asks you to specify a starting reference point, and then designate how many cells you want to move vertically (rows) and horizontally (columns) away from that intial reference. OFFSET then pulls the value you land on after making those moves.
The height and width syntax inputs at the end are optional and only used if you want to return a range larger than a single cell. For example, if you wanted Excel to return a range cells that’s 3 x 4 cells large, then you would input 4 and 3 respectively for those syntax inputs.
For this tutorial, we’ll only need to return a single cell, so we will not be using those syntax inputs.
The MATCH Formula
= MATCH ( lookup_value, lookup_array, 0 )
The MATCH formula asks you to specify a value within a range and returns the position of that value within that range. For example, using the table shown above, if I selected 2012 as my lookup value and selected the entire top row as my lookup array, the MATCH formula would return the number “2” because 2012 is the second value in the array I selected. Also note that at the very end of the syntax, you need to put in a “0” as the last argument to direct the MATCH formula to perform an exact match lookup.
Putting it All Together
Below is a simplified version of the syntax that you can use for reference when you want to remember the formula combination:
= OFFSET ( starting point, MATCH ( vertical lookup value, left hand lookup column excluding starting point, 0 ), MATCH ( horizontal lookup value, top header row excluding starting point, 0 ) )
Goal: Assume we want to find the Revenue amount for Brazil in the year 2014
Step 1: Start writing your OFFSET formula and select your starting reference point, which will be the upper left hand corner of your table. In this case it’s the cell containing the word “Country”.
Step 2: Start your MATCH formula and select your vertical lookup value, in this case, the country China
Step 3: Identify your vertical lookup array. This is your vertical column EXCLUDING the cell you originally selected as your starting point. In this case, it’s the cells with country names, highlighted in purple below.
Step 4: Close out your MATCH formula by inputting “0” for exact match.
Step 5: Start your MATCH formula and select your horizontal lookup value, in this case, the year 2015
Step 6: Identify your horizontal lookup array. This is your top row EXCLUDING the cell you originally selected as your starting point. In this case, it’s the cells with year values, highlighted in magenta below.
Please Note: Make sure that you do not overlap your MATCH selection arrays with your starting point. Note that in all of the selections above, none of them overlap or cross one another. This is because the MATCH formula informs your OFFSET formula regarding how many cells to move AWAY from the starting point. If you overlap the selection, it will make it move an extra step away from your target. This is probably the most common mistake made when using OFFSET MATCH MATCH. The lack of overlap is also one key difference that this formula combination has with INDEX MATCH MATCH.
Step 7: Close out your MATCH formula by inputting “0” for exact match
Step 8: Add one final parenthesis to close out the OFFSET MATCH MATCH combination formula
What Excel Does
The first thing Excel will do is process your MATCH formulas. Excel will determine the relative position of your lookup values within the lookup arrays you’ve selected. In this scenario, China and 2015 are both in the 4th position in their relative arrays. Excel now knows how many cells to move down and how many cells to move right.
Starting from your original starting cell of “Country”, Excel will move 4 cells down and 4 cells right. The resulting value for the entire formula combination is whatever cell Excel finally lands on, which in this case is $2,251.67.
The primary benefit of using OFFSET MATCH MATCH is that it is intuitive and that many people are familiar with the OFFSET formula. For those reasons, it’s important to at least consider this formula combination among your lookup options.