Excel OFFSET-XLOOKUP: Better Alternative to OFFSET-MATCH

In this tutorial, we will explore the purpose of OFFSET-MATCH in Excel and how OFFSET-XLOOKUP becomes a better alternative.

We use Excel’s OFFSET and MATCH combination to offset from a starting cell to a certain number of rows or columns based on the MATCH result. From that point, you can offset left, right, up, or down and return a single value or multiple values by specifying width and height.

The OFFSET and XLOOKUP combination can return similar results in Excel. However, the key difference is that the ‘reference,’ which is the OFFSET starting cell, is dynamic. The ‘reference’ is the XLOOKUP result itself.

The ‘reference’ is the key difference between OFFSET-MATCH and OFFSET-XLOOKUP in Excel. It’s a static cell reference in the former, whereas in the latter, it’s the lookup value.

In addition, XLOOKUP is a modern lookup function, and you can take advantage of its additional benefits. You can look up values from top to bottom, bottom to top, left to right, and right to left.

As a side note, VLOOKUP won’t work similarly with OFFSET. It seems to me that this is why the OFFSET and XLOOKUP combination is not yet familiar among Excel users. They might think that this combination won’t work.

Sample Data:

QuarterMonthBenLisa
Q1January50004500
February52004600
March53004700
Q2April54004800
May55004900
June56005000
Q3July57005100
August58005200
September59005300
Q4October60005400
November61005500
December62005600

Examples of the OFFSET and MATCH Combination in Excel

The above sample data in A1:D13 is a sales summary of two employees for one year. Let’s apply the OFFSET-MATCH combination to this data.

Example 1:

Let’s first extract the sales amount of Ben in October.

=OFFSET(B1, MATCH("October", B1:B13, 0)-1, 1)
OFFSET-MATCH example with single value output in Excel

The MATCH function here matches the key “October” in B1:B13 and returns the relative position of the keyword.

MATCH Syntax:

MATCH(lookup_value, lookup_array, [match_type])

Where lookup_value is “October”, lookup_array is B1:B13, and match_type is 0, which means an exact match of the lookup_value.

It would return 11. Now take a look at the OFFSET syntax:

OFFSET Syntax:

OFFSET(reference, rows, cols, [height], [width])

The reference here is cell B1 from which to start the offset of rows and columns.

  • rows is the MATCH result – 1 because the offset count starts from 0.
  • cols is 1 since we want the result from column C.

Example 2:

Actually, we don’t require the above type of lookup since we can use VLOOKUP for that.

=VLOOKUP("October", B1:C13, 2, 0)

But here is why the combination is a powerful alternative to VLOOKUP.

=OFFSET(A1, MATCH("Q4", A1:A13, 0)-1, 1, 3, 2)

This formula will return the Q4 sales of Ben.

OFFSET-MATCH example with multiple value output in Excel

The OFFSET returns the relative position of “Q4” in column A. The OFFSET offsets match-1 row and 1 column and returns 3 rows (height) and two columns (width).

This wouldn’t be doable with VLOOKUP in a regular way.

Note:

The above examples are for vertical lookups. If you perform a horizontal lookup, the MATCH formula should be used in the ‘cols’ argument of the OFFSET function.

Examples of the OFFSET and XLOOKUP Combination in Excel

The drawback of the above OFFSET and MATCH combination in Excel is that it offsets from a specified starting cell. If you want to go beyond static lookups, try the OFFSET and XLOOKUP combination.

First of all, XLOOKUP is currently not available in all versions of Excel. Please check the availability before testing. If you are using Excel 2019 or any earlier version, the following formulas won’t work.

To look up a value and offset around it, you can use this OFFSET-XLOOKUP combination formula:

=OFFSET(XLOOKUP("October", B2:B13, B2:B13), 0, 1)

The XLOOKUP function searches for “October” in the range B2:B13 and returns the value “October” from the same range.

XLOOKUP Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

Where lookup_value is “October”, lookup_array and return_array are B2:B13.

We can use the XLOOKUP result as the reference in OFFSET. In the above example, the OFFSET function offsets 0 rows and 1 column from the XLOOKUP result and returns Ben’s October sales.

The following formula searches for the key “Q4” and returns Ben’s sales in October, November, and December.

=OFFSET(XLOOKUP("Q4", A2:A13, A2:A13), 0, 1, 3, 2)

The XLOOKUP function searches for “Q4” in A2:A13 and returns “Q4”. The OFFSET function offsets 0 rows and 1 column. The height of rows is 3 and the width of columns is 2.

Wrap-up

In the OFFSET and MATCH combination, the reference to offset is a static cell reference. MATCH is used either to offset rows (in vertical lookups) or columns (in horizontal lookups), so it is not dynamic.

In the OFFSET and XLOOKUP combination, the reference to offset is the XLOOKUP result itself, making it more dynamic.

You will find these two combinations useful in scenarios where you need to return values from around the lookup value.

They are also helpful in handling merged cells in lookups. Regular lookup functions may match the value in the very first cell of the merged cells. You can look up that value and offset to get the values corresponding to the merged cell.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

Sort Each Row Individually in Excel Using a LAMBDA Formula

Sorting rows in Excel typically refers to rearranging entire datasets based on values in...

Sort by Field Labels Using the SORT and XMATCH Combo in Excel

Want to sort your Excel data by column names instead of column positions? Learn...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.