OFFSET and MATCH Functions in Excel
By Ken Burnside
Extracting data from tables in Excel is routinely done in Excel by way of the OFFSET and MATCH functions. The primary purpose of using OFFSET and MATCH is that in combination, they are much more useful than on their own. Think of them as building blocks that can be put together to achieve highly robust and flexible data lookups.
The MATCH Function
MATCH is a function that returns the position of the text references in the cell range referenced. The format is MATCH: value, range, [MATCH type]. MATCH will return the position of the value entered in the array, but it can only accept arrays that are a single column or a single row. The MATCH type argument is either 0 (for an exact match), -1 for a match that's equal to or greater than the value specified, or 1 for a match that's less than or equal to the value specified. When using MATCH to match a text value in an array, the MATCH type of 0 must be used.
The OFFSET Function
OFFSET is used to return a value from a position that's offset from a given cell reference. The format is OFFSET: cell reference, rows, columns, height, width. The cell reference can refer to a range of cells. Rows specifies the number of rows away from the cell indicated and, with a negative number, goes up, and columns offset, where if the number of columns is negative, it returns the values to the left of the indicated cell. OFFSET allows you to specify the height and width of the returned range in cells with the last two terms, which are optional.
Combining OFFSET and MATCH to Reference Data
The traditional use for combining OFFSET and MATCH is to use the MATCH function inside the OFFSET function to return the column and row number from a specific text match criterion. This allows for more flexibility than an HLOOKUP or VLOOKUP function can give.
Ken Burnside has been writing freelance since 1990, contributing to publications as diverse as "Pyramid" and "Training & Simulations Journal." A Microsoft MVP in Excel, he holds a Bachelor of Arts in English from the University of Alaska. He won the Origins Award for Attack Vector: Tactical, a board game about space combat.