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.
The Syntax
= 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.
Summary
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.
This is very good, but how will I find the name of the country which has maximum revenue in particular year. i.e. 2013
Please advise.
Thanks,
=INDEX($C$13:$C$18,MATCH(Max($E$13:$E$18),$D$13:$D$18,0))
Would obviously have to build on this formula if you wanted to be able to enter a year and have it pull the data, but that is the base formula to do what you ask.
This is a few years later Mike, but I could not get your formula to work at all.
Assuming the Revenue by Country and Year table starts in A1, my formula (based on yours) would be
=INDEX($A$2:$A$7,MATCH(MAX($B$2:$B$7),$B$2:$B$7,0))
Obviously we’d need to build on that as you say.
It’ll be nice to have the row and column headers to show which cell is C4, which row is C, etc
This is super helpful and the most straightforward / intuitive way I’ve seen it described. Thank you!
a bit off topic but bear with me pls.
i saw that offset works well with a lookup table or basically a range.
like offset(a1:a5, 0,3) and it will return d1:d5 range set. but what if i want it to work with more tables like =offset($A$1:$A$5,$D$1:$D$5,6,0)
from what i saw i does not support this. so is there another way ? the reason why i ask is that i have to define protected ranged in the review -> allow users to edit ranges section. and for some files they are even 80 ranges to define, each composed of a fix number of small lookup tables like (=$E$2:$H$6,$J$2:$M$6,$O$2:$S$6,$V$2:$Y$6,$AA$2:$AE$6,$AG$2:$AJ$6,$AM$2:$AP$6, $AR$2:$AV$6,$AX$2:$BA$6,$BD$2:$BG$6,$BI$2:$BM$6,$BO$2:$BR$6 for example). so i thought i would just define the 1st manually and then just offset the rest 50 – 80 ranges.
any thoughts?
Thank you – how can you apply it if there are multiple headings of the same text ie Year 1 and you want to sum them rather than just identify the first one?
Is it possible to return the name of the country based on the min and max for a particular year
Is it possible to match to 3 criterion in the rows and 1 criteria in the column and return the address of the cell?
=CELL(“address”,INDEX(A3:CV215,MATCH(1*(A3:A215=M219)*(B3:B215=M220)*(C3:C215=M221)*(L3:L215=A220),0)))
can somebody help me pinpoint what’s wrong in the above?Thank you
if theres two china in coloumn country, ex( c16 = china; c18 = china).
and first china (C16) value on D12 – G12 equal to 0
but second one (C18) value on D12 – G12 arent equal to 0.
what formula i need to use if i wanna lookup for china in D12 that not equal to 0.
thanks
This formula assumes you know the row and column of the information you want to look up, but how do you look for a specific value (say 855.75) “In a Table” and have it return the row and/or column number.
PS I already know how to do this for a column or row with Index Match, but am lost on how to do this in a table
I love it. Thanks for the great article. Learned a lot.
This helped me solve an problem that I was struggling with for hours. THANKS!!