🔗 Do you merge tables in Excel every day and it takes HOURS?
Imagine this: You have a sales table with product codes and another table with names and prices. You need to bring the product name into the sales table... or check if all codes exist in the master table. This is a task that the vast majority of Excel users perform daily.
Merging tables is used for:
- Bringing complementary data (customer name, product description, price...)
- Performing audits and reconciliations
- Detecting missing or inconsistent data between tables
- Summing, counting or averaging values based on conditions
But doing it manually with formulas can be slow, complex and error-prone. In this article I explain the main merge functions, their limitations and how Data Tools Suite automates everything in seconds.
📊 The classic functions for merging tables
1. VLOOKUP – The queen for years
The best-known and most used function. It searches for a value in the first column of a table and returns a value from the same row in another column.
Advantages: Simple for basic merges.
Limitations: Only searches in the first column, cannot look to the left and returns an error if no match is found.
There are hundreds of tutorials online explaining VLOOKUP in detail – it's the function everyone learns first!
2. XLOOKUP – The modern evolution
The successor to VLOOKUP (available since Excel 365 and 2021). Key improvements:
- Can search in any column and return values to the left
- Exact or approximate search by default
- More elegant error handling
- Search from the bottom up
If you use a recent version of Excel, XLOOKUP is the recommended choice for simple single-criterion merges.
3. Helper column technique – Multi-criteria merge with VLOOKUP
A widely used solution when you need to merge by more than one criterion (e.g. Branch + Product).
How it works:
- Create a helper column in the lookup table concatenating the criteria (e.g. =A2&"-"&B2).
- Do the same in the source table with the same criteria.
- Then use VLOOKUP searching for that combined key.
Important: The helper column must be the first column of the lookup table for VLOOKUP to work.
It is a valid and very common technique, but it has drawbacks: it alters the original data, complicates the file and any change forces recalculation of the keys. Many users get frustrated with this extra step.
4. INDEX + MATCH – The most powerful combination
The most flexible native alternative for multi-criteria merges without a helper column.
INDEX returns a value from a position, while MATCH finds the row (or column). Together they allow searching in any direction and with multiple criteria.
Basic example of two-criteria merge:=INDEX(return_table, MATCH(1, (criterion1=table_criterion1)*(criterion2=table_criterion2), 0), desired_column)
(Entered as an array formula with Ctrl+Shift+Enter in older versions).
Once mastered, it is very powerful, but the syntax scares many users and is prone to errors.
🔢 Other types of merge: Sum, count and conditional statistics
SUMIF and COUNTIF
Ideal for summing or counting values that meet a criterion in another table.
COUNTIF is especially useful for audits: it counts how many times a code appears in the master table → if the result is 0, the data does not exist.
SUMIFS and COUNTIFS
Advanced version for multiple criteria. Very powerful, but the syntax can be confusing.
Other useful functions
- AVERAGEIF and AVERAGEIFS
- MAXIFS and MINIFS (in recent versions)
Knowing they exist expands your possibilities, although they are used less than search functions.
⚡ The definitive solution: Merge Excel Tables from Data Tools Suite
All the above techniques require writing complex formulas, creating helper columns, testing, debugging errors... and repeating every time the data changes.
Merge Excel Tables from Data Tools Suite eliminates all that complexity:
- Select source table and lookup table
- Choose the key columns for the match (one or several, no helper column needed)
- Select the columns you want to bring
- One click and done!
Key advantages:
- You write no formula and do not alter the original data
- You can get the result as values or as formula (the app writes it perfectly for you)
- Multi-criteria merges intuitively
- Visual indicator of matches/no matches
- Massive time savings on repetitive tasks
See Merge Excel Tables in detail
Watch demo video:
Also related: the Conditional Formula app lets you enter any complex formula in a guided way, ideal when you need something very specific.
⏱️ Manual vs. Automated: The real savings
| Task | Manual formulas | Merge Excel Tables | Savings |
|---|---|---|---|
| Simple merge (1 criterion) | 5-15 minutes | 20 seconds | 95% |
| Multi-criteria merge (with helper column) | 30-60 minutes | 1 minute | 98% |
| Match audit | 20-40 minutes | 30 seconds | 97% |
| WEEKLY TOTAL (if done frequently) | 3-6 hours | 10 minutes | Up to $400/month |
💡 Conclusion: Merge tables without complications
VLOOKUP, XLOOKUP, helper columns and INDEX+MATCH are powerful tools... when you know how to use them. But for most professionals, writing and maintaining complex formulas (or altering data) consumes valuable time.
With Merge Excel Tables from Data Tools Suite you do the same (and more) without writing a single formula, without helper columns and with immediate results.
Combine it with the other suite apps (data cleaning, sends with MultiMail) and you'll have an unbeatable workflow.
Invest once and multiply your Excel productivity forever.
This article is a translation of the original in Spanish.
