ZackSoft XMatch - Powerful Lookup Function

Feedback

About Us

Home

Excel Add-Ins

C++ XLL

Freeware

Download

Purchase

Updates

Tutorial

XMatch Function
Dotted Line

This tool has been developed in C++ as an XLL add-in using Microsoft Excel 2007 SDK for maximum performance. It implements the XMATCH user defined function in conjunction with XMATCHDATA to perform very fast and advanced lookup.

If you have a lot of data in Excel from an external source, for example, SAP BW and you want to analyze the data and/or prepare report then you need a way to lookup the data easily and quickly. The two key criteria in choosing a lookup mechanism is flexibility and speed.

Key Features

Easily perform two dimensional multiple index lookup (up to a maximum of six indexes).
Automatic recalculation of XMATCHDATA if any cell in the data range changes, which in turn, will trigger recalculation of all dependent XMATCH formulas.
Smart lookup treats adjacent cell values as the same as the preceding cell to avoid repeating data thereby reducing spreadsheet size.
Provides better functionality than Excel’s VLOOKUP, HLOOKUP, INDEX and MATCH functions.
Will take advantage of Excel 2007’s multi threaded worksheet recalculation to run even faster.

Excel 2007 can use from 1 to 1024 concurrent threads for worksheet calculation. Earlier versions of Excel only uses a single thread for worksheet recalculations whether on single or multi core processor computers.

Comparison	XMatch	INDEX/MATCH
Two dimensional lookup	Yes	Yes
Multiple index lookup	Yes	No
Speed	Fast	Slow

Using INDEX/MATCH can also make the formula difficult to read and maintain. VBA and COM add-in user-defined functions are considered not thread safe and therefore only run in a single thread. VBA add-ins also have the disadvantage that cells with the VBA function will recalculate every time the workbook is opened.

If you have tens or even hundreds of thousands of lookup in one or many spreadsheets, then using XMatch will save you considerable time and may make the difference between meeting or missing deadlines.

Key Benefits

Perform very fast two dimensional multiple index lookups.
Reduce spreadsheet recalculation times and improve productivity.

Save time and costs in using XMatch to produce Excel reports from SAP BW 3.5x data instead of writing customised SAP query reports (just provide the query name, for example, SAPBEXqueries!SAPBEXq0001 to the Range_name parameter of XMATCHDATA).

XMATCHDATA()

Use this function to setup the data and specify the keys to use for lookups. An indexing mechanism will be created in-memory for efficient lookup by XMATCH.

XMATCHDATA Function

XMATCH()

This function will perform the lookup and return the value of the cell which matches the specified keys based on the corresponding XMATCHDATA’s cell reference.

XMATCH Function

XLOOKUP()

This function complement Excel's VLOOKUP and HLOOKUP function as it performs a two dimensional lookup using the top row and leftmost column of a table.

XLOOKUP Function

XOFFSET()

This function performs a non-volatile offset and returns a reference to a range that is offsetted by a given number of rows and columns and is within the boundary of the supplied reference.

XOFFSET Function