OFFSET and MATCH Functions in Excel

By Ken Burnside

OFFSET and MATCH can pull data out of large data sets easily.
i database on paper image by .shock from Fotolia.com

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.

×