Function Problems

How to Use INDEX, MATCH, and INDIRECT for Dynamic Excel Lookups

Tauseeq Magsi Oct 22, 2026 868 views

Question details

The user needs to create a dynamic formula using INDEX, MATCH, and INDIRECT to retrieve employee counts from different worksheets based on location and month, while handling data that starts at an offset column (column O).

How to Perform Dynamic Lookups Using INDEX, MATCH, and INDIRECT

Product: Spreadsheet
Device & OS: not provided
Scenario: Retrieving specific data points from multiple worksheets by dynamically selecting the sheet name and locating the correct row and column, while accounting for shifted data ranges.
Observed behavior: The dynamic formula requires a +14 column offset adjustment because the source data starts in column O (the 15th column) rather than column A.

Before you start

Ensure your worksheets are named exactly identically to the location criteria you will reference in your formula, and verify the exact starting column of your data to accurately calculate any necessary offsets.

Solution 1Recommended

Using INDEX, MATCH, and INDIRECT with a Column Offset

Construct a dynamic lookup formula that selects the correct worksheet using INDIRECT and finds the exact data point using INDEX and MATCH, adding a mathematical offset for data not starting in column A.

The INDIRECT function allows you to reference different worksheets dynamically based on a cell value. MATCH is then used to find the relative row or column position. When your data doesn't start in column A (for example, it starts in column O), you must add an offset to the MATCH column result so the INDEX function points to the correct absolute column.

Set up your reference cells

Create input cells for your criteria, such as 'Location' in cell A1 (to determine the worksheet name) and 'Month' in cell A2.

Write the INDIRECT reference

Use INDIRECT("'" & A1 & "'!A:Z") to dynamically refer to the entire data range in the specific location's worksheet.

Calculate the MATCH positions

Use MATCH to find the row for the specific data and the column for the month. For example, use MATCH(A2, INDIRECT("'" & A1 & "'!O1:Z1"), 0) to find the relative column position of the month within the data range.

Apply the column offset adjustment

If your INDEX array covers the whole sheet (Columns A:Z) but your MATCH function searches a range starting at column O (the 15th column), add +14 to your MATCH column result to align the positions. The formula portion will look like: MATCH(...) + 14.

Using INDEX, MATCH, and INDIRECT with a Column Offset

When to skip the offset: If your INDEX formula specifically references the exact range starting at column O (e.g., INDEX(O:Z, ...)) rather than the entire worksheet columns (INDEX(A:Z, ...)), the +14 offset is not necessary because the relative starting points match.

Advanced Spreadsheet Features

Master Dynamic Lookups Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like INDEX, MATCH, and INDIRECT, making it easy to build complex, dynamic data lookup models across multiple worksheets without compatibility issues.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple data worksheets and summary dashboard.
2. Enter the nested formula: Select the target cell and type your combined =INDEX(INDIRECT(...), MATCH(...), MATCH(...) + 14) formula to pull the correct data.
3. Evaluate the formula: Use the 'Evaluate Formula' tool in the Formulas tab to step through the INDIRECT references and MATCH calculations to ensure your column offsets are correct.

100% compatible with Microsoft Excel formulas and functionsFree to download and use for all basic and advanced spreadsheet tasksIntuitive interface for auditing and evaluating complex nested formulasSeamless handling of large datasets across multiple linked worksheets

Frequently Asked Questions

Why does my INDIRECT function return a #REF! error?

A #REF! error in INDIRECT usually happens if the worksheet name contains spaces but isn't enclosed in single quotes within the formula (e.g., INDIRECT("'" & A1 & "'!A1")), or if it references a workbook that is currently closed.

Do I always need to add a mathematical offset like +14 to MATCH?

No. You only add an offset if the range you are searching with MATCH is smaller or shifted compared to the larger array you are returning data from with INDEX. If both ranges start at the exact same column, no offset is needed.

Can I use XLOOKUP instead of INDEX and MATCH for this?

Yes, XLOOKUP is a more modern alternative that inherently handles ranges without requiring column counting or manual offsets. However, you will still need to combine it with INDIRECT if you want to dynamically switch between different worksheet tabs.

Excel & Data Problems