Merge Tables in Excel: From VLOOKUP to Automation

New Release 🚀 MultiMail 4.0 is here! Mode 5: Send multiple documents per client. Fast & Secure.

Merge and cross tables in Excel automatically without complex formulas — Merge Tables in Excel

Excel Productivity 🔗 Do you merge tables in Excel every day and it takes hours?

Imagine this: You have one table with sales by product code and another with product names, prices, and stock. You need to bring the product name and price into the sales table, or check if all codes exist in the master table. This is a task that most Excel users perform every day.

Merging tables is used to bring complementary data, perform audits, detect missing records, and much more. But doing it manually with formulas can be slow, complex, and error-prone.

Keep struggling with complicated formulas? ❌
Or automate table merging in seconds? ✅

📊 The Main Classic Ways to Merge Tables

🔎

1. VLOOKUP

The most well-known function for years. It searches in the first column and returns a value from the same row.

Advantages: Very simple for quick and basic merges.

Limitations: Only searches to the right, does not easily support multiple criteria, and breaks if columns are inserted or moved.

🚀

2. XLOOKUP

The modern successor to VLOOKUP (Excel 365 and 2021). It can search in any direction and return values to the left.

Advantages: More flexible, better error handling, and can search from the end.

Limitation: Not available in older versions of Excel.

🔑

3. Helper Column

A very common technique when you need to merge by more than one criterion (e.g., Branch + Product Code). A combined key is created in both tables.

Advantage: Allows using simple functions like VLOOKUP with multiple conditions.

Major disadvantage: It modifies the original data, complicates the file, and any change requires recalculating everything.

🔄

4. INDEX + MATCH

The most flexible combination. It allows lookups in any direction and with multiple criteria without a helper column.

Advantage: Very powerful and efficient in large files.

Disadvantage: Complex syntax that is hard to remember and prone to errors.

➕

5. SUMIF / COUNTIF and .IFS

Ideal for summing or counting values that meet one or multiple criteria in another table.

Advantage: Very useful for quick audits and summary reports (e.g., “Does this code exist?”).

Limitation: Syntax becomes complicated when there are many criteria.

📈

6. Conditional Statistics (MAXIFS, MINIFS, AVERAGEIFS)

Find the maximum, minimum, or average value based on specific conditions.

Advantage: Excellent for analysis and dashboards.

Limitation: Requires newer versions of Excel and the syntax is not always intuitive.

⚡ The Ultimate Solution: Merge Tables in Data Tools Suite

All the previous techniques require writing complex formulas, creating helper columns, and debugging errors repeatedly. Merge Tables from Data Tools Suite eliminates that complexity completely:

Merge up to 3 criteria without helper columns
Multiple merge types: lookup, sum, count, position, average, max, min, and more
Returns results as static values or as formulas (the tool writes perfect formulas for you)
Clear visual indicator of matches and non-matches
Massive time savings on repetitive tasks

See Merge Tables in detail →

Also related: The Create Conditional Formula tool lets you enter any complex formula in a guided way without errors. See Create Conditional Formula →

⏱️ Manual vs Automated: Real Time Savings

Task	Manual Formulas	Merge Tables
Simple merge (1 criterion)	5-15 minutes	20 seconds
Multiple criteria merge	30-60 minutes	1 minute
WEEKLY TOTAL	3-6 hours	10 minutes

📚 Recommended Resources on Merging Tables

📚 Want to keep improving your Excel productivity?

Check out our other articles where we solve real Excel problems with Data Tools Suite and MultiMail:

Go to Optipe Blog →

Merge tables without the hassle

Forget complicated formulas and helper columns. Automate your merges with Data Tools Suite.

🎯 Get Data Tools Suite – Free Trial

José Antonio de Diego – Founder of Optipe. Over 25 years helping companies work smarter with Excel.

Questions? Write to me at This email address is being protected from spambots. You need JavaScript enabled to view it.

📋 User Testimonials

César I. C., Director of University Admissions, Talca, Chile:

"Optipe has become an indispensable tool in my work; the time savings are significant when grouping tables and extracting data compared to doing it manually."

Pablo P. C., Editor and Entrepreneur in Ecotechnologies, Bariloche, Argentina:

"Optipe changed my life by allowing me to summarize tables into a single record to manage book shipments and sales, making pivot tables much easier to use."

Sarah M., HR Specialist, Toronto, Canada:

"MultiMail is perfect for sending personalized reports and reminders to the entire team in bulk from Excel. It reduces errors and saves me hours every month on internal communications."

Vanessa V., Database Specialist at Bank, San José, Costa Rica:

"Optipe makes it easy to remove blank cells, convert values, and present information in pivot tables quickly and safely; I couldn't perform my duties without it."

Jaime K., Independent Consultant, Lima, Peru:

"Optipe allows efficient and fast handling of tables, achieving spectacular results and saving time in database modeling for finance and risk."

Franz B., Data Engineer, Berlin, Germany:

"Optipe makes data cleaning and pivot table creation easy; it has greatly improved my productivity in engineering projects."

Gerardo F. D., Institutional Planning Analyst, Veracruz, Mexico:

"Optipe is an excellent tool for quickly managing databases and cleaning data, ensuring accurate reports; I highly recommend it."

Gerardo F., Planning Analyst, Veracruz, Mexico:

"Data Tools Suite is a very good tool for quickly managing databases, as well as for cleaning out data clutter, which ensures the correct presentation of various reports and results in general. I highly recommend this OPTIPE tool."

Carlos R. L., Analista de Recursos Humanos, Asunción, Paraguay:

"MultiMail allows me to send payroll data and certificates to over 200 employees monthly with just a few clicks. It used to take a full day, now it's done in 10 minutes. Huge time saver!"

Giulia R., Sales Manager, Rome, Italy:

"Using Optipe, I consolidate sales data from multiple files in seconds, allowing me to focus on strategies instead of manual tasks."

José Luis D., Data Analyst, Madrid, Spain:

"Every month and quarter, I have to extract and analyze data in Excel files, sometimes very large and with a high amount and complexity of calculations. Optipe is a great help because it reduces the time of all the calculations and my work. I recommend it to anyone who regularly handles files of all types, but especially those with large amounts of data."

George K., New York, NY, USA:

"I spend more on any company meal than buying Optipe’s apps; they’ve paid for themselves in days or less. It’s incredible the time we save on data segmentation and email sending."

Emily R., Chicago, IL, USA:

"I used to spend hours and hours every week preparing data for analysis, and now I do it in minutes with Data Tools Suite. It’s truly fantastic."

Raj P., IT Analyst, Singapore, Singapore:

"Optipe optimizes my Excel workflow for IT projects; quick data normalization and summaries are a game-changer in Asia."

Sophie L., Business Consultant, Paris, France:

"Optipe is an essential tool for handling large data volumes in consulting; it reduces errors and speeds up analyses for my clients."

Betzabé G., Technical-Sales Manager, Caldas/MG, Brazil:

"I saw the features of the Multimail add-on and found them to be extremely useful, since I have to send personalized emails to many clients."

Sergio A. C., Excel MVP, Mexico:

"I use Optipe daily to efficiently manage databases, saving time on summaries and data normalization."

Rogelio A. C., Specialist in Technological Innovation and IT Reengineering, Pachuca, Mexico:

"Optipe is the perfect solution for managing databases simply and efficiently, making complex processes easy and becoming an essential part of daily work."

John C., University Professor, Glasgow, Scotland:

"I manage data that I pull from a system into Excel, and I always have to clean it up first because it's riddled with extra spaces and blanks. Then I need to consolidate a bunch of those tables into a single file before I can run my analyses. With Data Tools Suite, I get it all done so quickly now—I can hardly believe it!"

Emma W., Marketing Coordinator, London, United Kingdom:

"With MultiMail, I send personalized campaigns to hundreds of clients from Excel in minutes, without complications or errors. It has transformed how I manage my newsletters and follow-ups."

Héctor O. R., Director of Administration and Operations, Santiago, Chile:

"Optipe has been incredibly useful for data analysis, saving time and being a powerful and easy-to-use tool; once you start using it, you can't abandon it. It literally changed my life."

Ana S. M., Data Manager, São Paulo, Brazil:

"Optipe has revolutionized my work with databases; now I process reports in minutes instead of hours. It's indispensable for any analyst!"

Eduardo A., Head of Human Resources, Talca, Chile:

"I send monthly fee and honoraria data to 500 contractors, something that used to take me a full day. With MultiMail I handle it in under 10 minutes. A huge change!"

Michael T., Financial Analyst, New York, United States:

"With Optipe, I clean and analyze complex data in Excel quickly, saving hours weekly on my financial reports. Highly recommended!"

José A. C., Medical Director, Madrid, Spain:

"Every month I receive a large number of reports with medical records. Using Data Tools Suite, I can consolidate them into a single file in a matter of a minute, and together with Multimail, I can send the corresponding reports. Discovering and using it has meant a huge leap in productivity and safety for me and our entire department."