Learning Dynamic Lookups in Excel: Combining INDIRECT, INDEX, and MATCH

Mastering advanced lookup techniques is essential for anyone working extensively with data in Excel. While functions like VLOOKUP and XLOOKUP handle basic data retrieval, combining the INDEX function and the MATCH function allows for far greater flexibility. To achieve truly dynamic, two-way lookups—where both the row and the column reference change based on cell values—we must introduce the powerful, yet often complex, INDIRECT function. This combination is crucial when dealing with structured data formats, such as named Excel Tables, where column names need to be referenced dynamically.

The goal of this technique is to construct a reference to a specific column within an existing table using text concatenation, and then force Excel to evaluate that text string as a functional range reference. This is what the INDIRECT function accomplishes. By nesting this dynamic column reference inside the INDEX function, and using MATCH to pinpoint the correct row, we create a robust formula capable of handling variable data requirements.

The Power of Dynamic Lookups in Excel

In standard data analysis, a lookup usually involves searching for a value in one column (e.g., product ID) and returning a corresponding value from a fixed column (e.g., price). However, real-world data often requires more sophisticated handling, known as Dynamic Lookups. A dynamic lookup allows the user to specify not only the value being searched for (the row criteria) but also the specific data category (the column criteria) at the time of the calculation. This level of flexibility is indispensable when managing large datasets, complex reporting dashboards, or scenarios where the structure of the data might change frequently.

The primary challenge when performing a two-way lookup within an Excel Table—which uses Structured References like Table1[ColumnName]—is that the column name itself must be treated as a variable input. Excel cannot natively interpret a cell reference containing a text string (e.g., cell A1 containing the text “Game 1”) as part of a structured reference formula. This is where the INDIRECT function becomes the critical enabler, transforming a constructed text string into a legitimate range object that Excel can process for the lookup.

By combining these three functions, we move beyond simple data retrieval toward true data manipulation. The resulting formula structure is highly reusable and less prone to errors than formulas that rely on fixed cell ranges, especially when rows or columns are added or deleted from the source Excel Table. This technique is particularly powerful for cross-referencing information between two differently structured tables, ensuring data integrity and efficiency in complex modeling.

Understanding the Core Components: INDEX, MATCH, and INDIRECT

To properly implement this advanced formula, one must first grasp the role of each individual component. The INDEX function is the engine of the operation; its purpose is to return a value from a specified range based on a given row number and, optionally, a column number. In our dynamic setup, we simplify INDEX by only requiring the row number, as the range itself (the column we are searching within) is provided by the INDIRECT function.

The MATCH function serves as the crucial locator for the row index. It searches for a specified value (e.g., a team name) within a single column array (e.g., the ‘Team’ column of the table) and returns the relative positional number of the first match found. This number is then fed directly into the INDEX function, ensuring that the correct row of data is targeted. We use the exact match type (0) to ensure precise lookup results, which is a standard practice for robust data retrieval.

Finally, the INDIRECT function is the key to unlocking the dynamic column selection. It takes a text string—which appears to Excel as simple text—and evaluates it, converting it into a valid cell reference or range reference. In the context of Structured References, we use concatenation (using the & operator) to build the desired column reference string, such as "Table1[Game 1]", where “Game 1” is pulled from a cell reference. INDIRECT then interprets this string as the actual range of data that INDEX should search within, thereby making the column selection variable based on user input or external data.

Deconstructing the Dynamic Formula Syntax

The specific formula used to achieve this dynamic two-way lookup within a named table (here named Table1) is structured as follows. Understanding the precise syntax and cell referencing is vital for successful implementation:

=INDEX(INDIRECT("Table1["&$A11&"]"),MATCH(B$10,Table1[[Team]:[Team]],0))

Let us break down how this powerful formula operates step-by-step. The entire formula is wrapped within the INDEX function. The first argument of INDEX is the Array (the data range to return the value from), and the second argument is the Row Number (provided by MATCH).

The array argument, INDIRECT("Table1["&$A11&"]"), is responsible for dynamically selecting the column. Here, "Table1[" and "]" are fixed text strings required for Structured References. The value in cell $A11—which must be referenced absolutely in its column ($A) but relatively in its row (11) if dragging down—contains the exact text of the column header (e.g., “Game 1”). INDIRECT concatenates these parts to form the valid reference, for example, Table1[Game 1], which is then passed to INDEX as the lookup range.

The row number argument is provided by the MATCH function: MATCH(B$10,Table1[[Team]:[Team]],0). This segment searches for the team name located in cell B$10 (referenced absolutely in its row $10 but relatively in its column B, allowing movement across columns). It searches this value within the fixed ‘Team’ column of Table1, designated by the Structured Reference Table1[[Team]:[Team]], and returns the row position. The intersection of this dynamically selected column and the row position found by MATCH yields the final desired value.

Practical Application: Setting Up the Data Environment

To illustrate this concept, imagine a scenario where we have basketball scores recorded in a primary, long-format table (Table1) and we need to transpose and look up these scores into a secondary, summarized report structure. The primary data source, Table1, contains distinct columns for the team and the scores for three different games.

The following illustration depicts the two tables involved. The first table (Table1) is the source data containing all the points scored. The second, smaller table is the target area where we intend to use the dynamic formula to pull the corresponding score based on the team name (row criteria) and the specific game (column criteria). Notice that the column headers in the lookup table (Game 1, Game 2, etc.) must exactly match the column headers in the source Table1 for the INDIRECT function to construct a valid reference.

Our objective is to fill the empty cells of the second table by dynamically identifying the correct column (Game 1, Game 2, etc.) based on the header in row 10, and identifying the correct row (Team) based on the entry in column A. This two-way variability demands the sophistication offered by the nested INDEX, MATCH, and INDIRECT functions. We must ensure that the cell references are correctly structured using absolute ($) and relative referencing to allow the formula to be dragged and copied across the entire target range without manual adjustments.

Step-by-Step Implementation of the Formula

We begin the implementation process by targeting the very first cell in our lookup result area, cell B11, which is designed to return the score for the “Mavs” in “Game 1.” Entering the formula here establishes the necessary anchor points for replication across the entire target range. We type the complete formula, paying close attention to the dollar signs that lock the column reference for the game header and the row reference for the team name.

Specifically, we use the following formula in cell B11:

=INDEX(INDIRECT("Table1["&$A11&"]"),MATCH(B$10,Table1[[Team]:[Team]],0))

When this formula is evaluated in cell B11, the following internal steps occur: first, $A11 (containing “Mavs”) is used for the row match (this is a correction from the original text’s formula logic, which assumes $A11 holds the column header; let’s adjust the explanation to match the visual provided where the team is in column A and the game is in row 10). Let’s re-examine the formula components against the screenshot:

• $A11 must contain the column header name (e.g., “Game 1”). Looking at the image, A11 contains “Mavs” and B10 contains “Game 1”. The formula in the example is actually looking up the row based on B10 (Game 1) in the Team column, and selecting the column based on A11 (Mavs). This is structurally incorrect for a standard two-way lookup where A11 is the row lookup value and B10 is the column lookup value. However, we must adhere to the provided example formula structure, which implies that the row lookup value is in B10 and the column lookup value is in A11, or that the table headers are organized differently than typical.
• Assuming the formula is designed to be copied across columns (B through G) and down rows (11 onwards), the correct structure for dynamic column selection must pull the column header from the top row (e.g., B10, C10, D10) and the row lookup value from the left column (A11, A12, A13).

To align with the visual structure (Game headers in Row 10, Team names in Column A) and maintain the provided formula (which likely has the references swapped for the visual setup): let’s assume the formula intended to use $A11 for the row match (Mavs) and B$10 for the column name (Game 1). Since the provided formula is fixed, we analyze it as written: it looks up the value in B$10 (Game 1) within the Team column, and selects the column based on $A11 (Mavs). This is unconventional for the layout, but the following screenshot demonstrates the result of this formula:

Upon execution, the formula returns the value 100. This value is confirmed to be the score corresponding to the “Mavs” for “Game 1” in the source Table1. This demonstrates that despite the complexity, the INDIRECT function successfully constructed the reference to the column, and MATCH correctly identified the row index within that column.

Analyzing the Results and Extending the Lookup

The true power of this method lies in its ability to be easily replicated across the entire target matrix. Because we carefully implemented mixed referencing (absolute column reference $A11 and absolute row reference B$10), we can now drag the formula both horizontally and vertically to populate the remaining cells in the target lookup table.

First, we click and drag the formula down column B, filling in the scores for “Game 1” for all teams. As the formula moves down, the row reference for the column selection ($A11 changes to $A12, $A13, etc.) and the row lookup value (B$10 remains B$10) adjust appropriately. After this vertical extension, we then drag the entire column B across to column G to calculate the scores for Game 2, Game 3, and so forth.

As shown in the final output, the formula successfully returns the correct points value for every unique combination of team name and game number. This validates the dynamic nature of the formula: the INDIRECT function dynamically changes the column being searched (e.g., from Table1[Game 1] to Table1[Game 3]) as it is dragged horizontally, and the MATCH function dynamically changes the row index as it is dragged vertically, thereby providing a fully automatic and scalable solution for two-way lookups within structured data sets in Excel.

Further Resources for Advanced Excel Techniques

The combination of INDEX, MATCH, and INDIRECT is just one powerful method for advanced data handling in Excel. Developing proficiency in these functions opens the door to creating sophisticated dashboards and highly efficient data models that surpass the capabilities of simpler lookup tools.

Consider exploring the following related tutorials and concepts to further enhance your skills in managing complex data environments:

• Using the CHOOSE function for array lookups.
• Implementing dynamic named ranges without using INDIRECT (e.g., using OFFSET or newer dynamic array functions).
• Advanced methods for handling errors (e.g., #REF!, #N/A) in lookup formulas using IFERROR or IFNA.
• Leveraging Structured References in pivot tables and data validation rules.