Learn How to Use VLOOKUP with Multiple Lookup Tables in Excel

When working with large datasets in Excel, analysts often encounter situations where necessary data is distributed across two or more separate ranges or “lookup tables.” While the standard VLOOKUP function is powerful for single-table lookups, it natively lacks the capacity to seamlessly search sequentially across multiple ranges. Fortunately, by nesting VLOOKUP within the IFERROR function, we can create a robust and reliable formula capable of querying multiple tables until a match is successfully found. This technique significantly enhances the flexibility and efficiency of data retrieval in complex spreadsheet models.

The Dual-Lookup Solution: Combining IFERROR and VLOOKUP

The standard methodology for conducting lookups across multiple tables relies on the error-handling capabilities of the IFERROR function. If the first attempt to retrieve a value using VLOOKUP results in an error—specifically the #N/A error, which signals that the lookup value was not located in the specified table array—the IFERROR function intercepts this failure. Instead of displaying the error, it executes a second, alternative action, which in this case is another VLOOKUP search against a secondary table range. This chain of logic allows the formula to sequentially check multiple sources for the required identifier.

The core structure of this powerful, multi-table formula is demonstrated below. This specific implementation is designed to handle two distinct lookup ranges, searching the first range, and if unsuccessful, automatically transitioning to search the second range without interrupting the user experience with an error message. It is critical that the structure of the lookup tables remains consistent, typically requiring the lookup key (the value being searched for) to reside in the leftmost column of both table arrays.

You can use the following VLOOKUP formula in Excel to use multiple lookup tables:

=IFERROR(VLOOKUP(G2,A2:B7,2,0),VLOOKUP(G2,D2:E7,2,0))

This particular formula attempts to look up the specific value in cell G2 in the first column of the range A2:B7 and return the corresponding value from the second column.

If the value in cell G2 cannot be found in the first lookup table, then the formula will try to look for it in the first column of the range D2:E7 and return the corresponding value from the second column of that range. This conditional logic is what enables the multi-table search functionality.

The following example shows how to use this formula in practice, utilizing basketball conference data to demonstrate the seamless transition between lookup ranges.

Example: Use VLOOKUP with Multiple Lookup Tables in Excel

To illustrate the practical utility of this formula, consider a scenario involving sports data where team statistics are segregated by conference. Suppose we maintain two separate tables in Excel: one dedicated to the Western conference basketball teams and their respective points, and another table listing the Eastern conference teams and their points. This segregation is common in business intelligence or reporting environments where data is naturally grouped.

Our objective is to create a single input mechanism where a user can type any team name, regardless of conference, and the system automatically retrieves the correct points value by searching both the Western and Eastern conference tables sequentially. The use of the dual-VLOOKUP structure is essential here, as a standard VLOOKUP could only analyze one conference table at a time. The setup below visually represents the distinct data tables we will be querying:

In this example, the Western Conference table occupies the range A2:B7, while the Eastern Conference table is located in the range D2:E7. The crucial element for our lookup is the column containing the team names, which must be the first column in both respective ranges (Column A and Column D). We designate cell G2 as the input cell for the team name we wish to search for, and cell H2 as the output cell where the calculated points value will appear.

Suppose we would like to look up the team name Kings in either table and return their corresponding points value. Since Kings is in the first table, the VLOOKUP will succeed on its initial attempt, bypassing the need to check the second table.

Executing the Sequential Lookup Formula

We can specify the Kings as the team name to look up in cell G2 and then type the following formula into cell H2. This single formula now governs the search across both Western and Eastern Conference tables:

=IFERROR(VLOOKUP(G2,A2:B7,2,0),VLOOKUP(G2,D2:E7,2,0))

The following screenshot shows how to use this formula in practice, confirming the successful retrieval of data for a team located in the primary range:

The formula successfully returns 19, which is the correct points value that corresponds to the Kings. In this scenario, the first VLOOKUP was successful, meaning the IFERROR function returned the result of the first lookup and did not proceed to the second argument.

If we change the name of the lookup team in cell G2 to a team found only in the secondary table, the formula will automatically update, demonstrating the powerful error-trapping mechanism. This is where the true value of nesting VLOOKUP within IFERROR becomes apparent.

For example, suppose we change the team name to Cavs, which is located in the Eastern Conference table (D2:E7):

How This Formula Works

Understanding the exact sequence of evaluation is key to mastering this multi-table technique. Let us reiterate the formula used to look up the team name in cell G2 using the combined power of two VLOOKUP functions and the encompassing IFERROR logic. This approach transforms a single-range function into a versatile, sequential search tool:

Recall the formula that we used to look up the team name in cell G2 using multiple lookup tables:

=IFERROR(VLOOKUP(G2,A2:B7,2,0),VLOOKUP(G2,D2:E7,2,0))

Here is how this formula works step-by-step:

• First, we use VLOOKUP to attempt to look up the value in cell G2 (e.g., “Cavs”) in the primary range A2:B7 (Western Conference data).

• Since “Cavs” is not in the Western Conference table, this first operation returns the error value #N/A.

• Then we use the IFERROR function to check if the result from the first VLOOKUP returned an error or not.

• If #N/A is returned, the IFERROR function executes its second argument, which is the secondary VLOOKUP function. This function then looks up the value in cell G2 in the next range of D2:E7 (Eastern Conference data).

• Because “Cavs” is found in the second range, the secondary VLOOKUP executes successfully, and its result (the corresponding points value) is returned by the overall formula, overriding the initial error.

Advanced Considerations and Alternatives

While the IFERROR structure is highly effective and backward-compatible with older versions of Excel, users should be aware of modern alternatives that offer even greater flexibility and cleaner syntax, particularly when dealing with an increasing number of lookup tables. Functions such as XLOOKUP, available in Microsoft 365 and newer desktop versions, allow for native multi-range searching without relying on error trapping.

For users constrained by older software or those who prefer the robust simplicity of the classic VLOOKUP, mastering the nested IFERROR technique remains a fundamental skill. It solves the common business problem of fragmented data sources by consolidating the search process into a single, efficient cell formula. This methodology can be extended to search three or more tables by nesting additional IFERROR and VLOOKUP pairs, although complexity increases rapidly with each added table.

Additional Resources

The following tutorials explain how to perform other common operations in Excel, building upon the foundational knowledge of lookup and error-handling functions:

• Guide to using the INDEX and MATCH functions for two-way lookups, offering a flexible alternative to VLOOKUP.

• Tutorial on dynamic range selection using OFFSET and COUNTA for more adaptive lookup tables.

• Comprehensive guide to understanding and resolving common spreadsheet errors, including the critical #N/A condition and other error types.