VLOOKUP with 2 Lookup Tables: How to Combine Them

Yes, you can use VLOOKUP with two separate lookup tables in Excel. The standard approach is to use a single VLOOKUP that checks the first table, and if it returns an error (meaning the value wasn’t found), you run a second VLOOKUP against the second table. This is typically done with the IFERROR function, which lets you chain lookups: =IFERROR(VLOOKUP(lookup_value, table1, col, FALSE), VLOOKUP(lookup_value, table2, col, FALSE)). That formula tries table1 first, and only if the value is missing there does it look in table2.

Formula

The core formula for a two-table VLOOKUP is:

=IFERROR(VLOOKUP(A2, Table1, 2, FALSE), VLOOKUP(A2, Table2, 2, FALSE))

Where:

• A2 – the lookup value you are searching for (e.g., a product ID, employee number, or part code).
• Table1 – the first named range or absolute reference (e.g., $B$3:$C$100).
• Table2 – the second named range or absolute reference.
• 2 – the column number in each table that contains the value you want to return.
• FALSE – forces an exact match. Always use FALSE for two-table lookups unless you have a specific reason not to.

The IFERROR function works by evaluating the first VLOOKUP. If it succeeds, it returns that result. If it fails (because the lookup value is not in Table1), it evaluates the second VLOOKUP. If the value is in neither table, the second VLOOKUP returns #N/A.

Worked Example

Suppose you have two product tables:

• Table1 (A1:B5): IDs 1001–1004 with prices $10, $12, $15, $18.
• Table2 (D1:E4): IDs 2001–2004 with prices $5, $7, $9, $11.

You want to look up ID 1001. The formula tries Table1 first: VLOOKUP(1001, A1:B5, 2, FALSE) returns $10. Since that works, IFERROR returns $10.

Now look up ID 2003. The first VLOOKUP searches Table1 for 2003, finds nothing, and returns #N/A. IFERROR then evaluates the second VLOOKUP: VLOOKUP(2003, D1:E4, 2, FALSE) returns $9. The final result is $9.

If you look up ID 9999 (in neither table), the first VLOOKUP fails, the second fails, and the formula returns #N/A.

Why Use Two Tables Instead of One?

In real work, data often lives in separate sheets or workbooks. For example, a company might keep current employees in one table and former employees in another. Or a store might have a price list for regular items and a separate clearance list. Merging them into one table is sometimes possible, but not always:

• The tables may have different column orders.
• They may be updated by different people or systems.
• You may not have permission to alter the source files.
• The combined table would be too large or slow.

Using two VLOOKUPs with IFERROR avoids copying or merging data. It also keeps your formulas dynamic – if either table changes, the lookup updates automatically.

How to Choose Which Table Comes First

The order matters. The first table is checked first, so put the table that is more likely to contain the lookup value first. This improves performance and avoids unnecessary lookups. For example, if you are looking up active employees, put the active employee table first and the former employee table second.

If you are not sure which table is more likely, consider the consequence of a wrong match. If both tables contain the same ID but with different values, the first table wins. That is a common source of errors. Always verify that IDs are unique within each table and that you intend the first table to take priority.

Common Problems and How to Fix Them

Here are the most frequent issues when using VLOOKUP with two tables:

• #N/A from both tables – The value is simply not in either table. Double-check for extra spaces, text-formatted numbers, or leading zeros.
• Wrong column index – If you use 2 but the return value is in column 3, you get the wrong data. Count columns from the left of the lookup range, not from the sheet.
• Using TRUE instead of FALSE – TRUE performs an approximate match, which can return a nearby value instead of an exact match. Always use FALSE for two-table lookups.
• Merged cells or hidden rows – These can shift ranges and cause incorrect results. Use named ranges to keep references stable.
• Case sensitivity – VLOOKUP is not case-sensitive. If you need case-sensitive matching, use XLOOKUP with the match mode argument or an INDEX/MATCH with EXACT.

Alternatives to IFERROR + VLOOKUP

While IFERROR + VLOOKUP is the classic method, newer Excel versions offer better options:

• XLOOKUP – Available in Excel 365 and 2021. You can nest two XLOOKUPs: =IFERROR(XLOOKUP(A2, Table1[ID], Table1[Price]), XLOOKUP(A2, Table2[ID], Table2[Price])). XLOOKUP has an optional if_not_found argument, so you can write =XLOOKUP(A2, Table1[ID], Table1[Price], XLOOKUP(A2, Table2[ID], Table2[Price])) without IFERROR.
• INDEX/MATCH – Works in all versions and is more flexible for left lookups. The two-table version is: =IFERROR(INDEX(Table1[Price], MATCH(A2, Table1[ID], 0)), INDEX(Table2[Price], MATCH(A2, Table2[ID], 0))).
• Power Query – If you frequently combine two tables, consider loading both into Power Query and appending them into one table. This is more maintainable for large datasets.

Frequently Asked Questions

Can I use VLOOKUP with more than two tables?

Yes, you can nest multiple IFERRORs. For three tables, use =IFERROR(VLOOKUP(A2, Table1, 2, FALSE), IFERROR(VLOOKUP(A2, Table2, 2, FALSE), VLOOKUP(A2, Table3, 2, FALSE))). It gets harder to read, so for more than two tables consider using XLOOKUP with its if_not_found argument, or restructure your data into a single table.

Should I use TRUE or FALSE for approximate match?

Always use FALSE unless you are intentionally looking up ranges (e.g., tax brackets or discount tiers). With two tables, FALSE is almost always correct. TRUE can return a value from the wrong row if the lookup value is between two entries.

What if the same ID exists in both tables with different values?

The first table in the formula wins. If you want the second table to take priority, simply reverse the order of the VLOOKUPs. Be aware of this when designing your formula – it is a common source of unexpected results.

Does VLOOKUP work with tables on different sheets?

Yes, as long as you reference the sheet name, e.g., VLOOKUP(A2, 'Sheet2'!$A$1:$B$10, 2, FALSE). The same IFERROR nesting works across sheets. Make sure the sheet name with spaces is enclosed in single quotes.

Why is my VLOOKUP returning the wrong value even though the ID exists?

Check that the ID is truly unique in the first table. If there are duplicate IDs, VLOOKUP returns the first match. Also verify that the column index number is correct and that the range starts at the lookup column. Finally, ensure there are no leading or trailing spaces – use TRIM() on the lookup value or the data.

Is there a performance difference between VLOOKUP and XLOOKUP for two tables?

For small tables, the difference is negligible. For large tables (tens of thousands of rows), XLOOKUP is generally faster because it uses a more efficient search algorithm. Also, XLOOKUP does not require the lookup column to be the first column of the range, which can simplify your setup.

Related

• Mac Start Up Automatically After Power Failure?
• Expose Self-Hosted Service Securely Without Cloudflare Tunnel
• Nano Delivery for Functional Foods: What It Can and Can’t Do

in lookup | Lookup value | - | 1001 | 0
in t1_rows | Rows in Table 1 | - | 50 | 1 | 100000
in t2_rows | Rows in Table 2 | - | 30 | 1 | 100000
out found | Value found | - | lookup + 0 * sqrt((lookup > t1_rows) * (lookup - t1_rows)) | 0