How to Use OFFSET MATCH MATCH

by Matthew Kuo on October 24, 2015

in Database Theory, Excel

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the most popular Excel books on Amazon
 

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.

OFFSETMATCHMATCH_02

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 valueleft 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”.

OFFSETMATCHMATCH_03

Step 2:  Start your MATCH formula and select your vertical lookup value, in this case, the country China

OFFSETMATCHMATCH_04

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.

OFFSETMATCHMATCH_05

Step 4:  Close out your MATCH formula by inputting “0” for exact match.

OFFSETMATCHMATCH_06

Step 5:  Start your MATCH formula and select your horizontal lookup value, in this case, the year 2015

OFFSETMATCHMATCH_07

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.

OFFSETMATCHMATCH_08

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

OFFSETMATCHMATCH_09

Step 8:  Add one final parenthesis to close out the OFFSET MATCH MATCH combination formula

OFFSETMATCHMATCH_10

 

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.

OFFSETMATCHMATCH_11

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.

OFFSETMATCHMATCH_12

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.

{ 7 comments… read them below or add one }

Buland August 10, 2016 at 3:53 am

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,

Reply

Mike H October 27, 2016 at 3:38 pm

=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.

Reply

PEI September 1, 2016 at 4:35 pm

It’ll be nice to have the row and column headers to show which cell is C4, which row is C, etc

Reply

Jess December 7, 2016 at 12:52 pm

This is super helpful and the most straightforward / intuitive way I’ve seen it described. Thank you!

Reply

tudor January 5, 2017 at 3:24 pm

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?

Reply

hannah January 15, 2017 at 2:31 pm

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?

Reply

Ryan February 22, 2017 at 7:16 am

Is it possible to return the name of the country based on the min and max for a particular year

Reply

Leave a Comment

{ 1 trackback }

Previous post:

Next post:

\n