How to Join Two Excel Files Without VLOOKUP (Step-by-Step)

You can join two Excel files without VLOOKUP in three ways. XLOOKUP fixes VLOOKUP's direction and matching problems. Power Query's Merge performs a real join and refreshes when the files change. An AI data agent lets you describe the join in natural language and skip the formula entirely. Which one fits depends on whether you need the joined table or the answer behind it.

The task itself is everywhere. You have a customer list in one file and an order export in another, and the only thing connecting them is an email address or an account ID. You need them in one view before you can answer anything useful.

VLOOKUP is the formula everyone reaches for, and it is also the formula everyone eventually gets burned by. Here are the alternatives, in the order of how much Excel you want to think about.

What joining two files actually means

A join matches rows from two tables using a shared key, then brings columns from one into the other. Three decisions define it, and getting any of them wrong is what produces a wrong answer that looks right.

Which column is the key? Email, order ID, SKU, account number. It has to mean the same thing on both sides.

What happens to rows that do not match? Keep every customer even when they have no orders, or keep only customers who ordered? These are different questions with different answers, and Excel will happily give you either one without asking.

Can the key repeat? One customer with five orders means one row on the left and five on the right. Whether you want five rows or one summarised row changes the entire result.

Answer those three before you write anything. Most broken joins are not formula errors. They are unstated assumptions.

The native ways to join two Excel files

Option 1: VLOOKUP, and why it keeps breaking

VLOOKUP searches the leftmost column of a range and returns a value from a column to the right, identified by a position number. That design creates four well-known traps, all documented on Microsoft's VLOOKUP function reference.

• It cannot look left. If your key sits to the right of the value you want, you have to rearrange the source file first.
• The column index is a hardcoded number. Insert a column inside the lookup range and the formula keeps pointing at position 4, which is now a different field. No error appears. The numbers just change.
• The match type defaults to approximate. Leave the last argument off and VLOOKUP looks for the closest match on data it assumes is sorted. On unsorted data it returns a confidently wrong value.
• It returns the first match only. If your key repeats, you get row one and no warning that rows two through five existed.

VLOOKUP is not bad. It is a 1980s-era design being asked to do a database job, and it fails quietly rather than loudly, which is the worst way to fail.

Option 2: XLOOKUP

XLOOKUP is the modern replacement, and it removes three of those four traps. It searches in any direction and defaults to an exact match. It takes a proper if_not_found argument instead of leaving #N/A in your sheet. And it references a column range rather than a position number, so inserting columns does not silently break it. Microsoft's XLOOKUP reference has the syntax.

The remaining limit is the same one VLOOKUP has: it is still a lookup, not a join. It pulls one value per row. Repeated keys still return only the first hit, and you are still maintaining a formula across thousands of rows in a file someone else will open next quarter.

Option 3: Power Query Merge, the real native answer

If you want an actual join in Excel, Power Query's Merge is it. Load both files as queries, choose Merge Queries, then pick the key column on each side. Now select the join kind: left outer keeps everything on the left, inner keeps only matches, full outer keeps both sides, and anti isolates the rows that failed to match.

That anti join is the underrated one. It answers "which customers in my list have no orders at all" in a single step, which is tedious to establish with lookups. Merge also refreshes, so next month's files flow through the same join without you rebuilding it.

The cost is the learning curve. Query steps, expanded table columns, and join kinds are all worth knowing. They are also four or five concepts between you and a question you could have asked in a sentence.

Where all three run out of road

Every native route shares the same three ceilings.

Keys are rarely clean. john@acme.com and John@Acme.com are the same customer and no exact match will agree. Real keys carry trailing spaces, inconsistent case, numbers stored as text, and IDs with a stray apostrophe from an old export. Every native method needs you to normalise the key first, and none of them tell you that is why your match rate is 60%.

The joined table is not the answer. Nobody wants a merged sheet. They want to know which segment is growing, which accounts churned, or which SKU carries the margin. The join is plumbing, and the plumbing is where most of the time goes.

The next person inherits your formulas. A workbook full of nested lookups is a maintenance liability. It works until a column moves.

How to join two Excel files with Powerdrill Bloom

Powerdrill Bloom treats the join as part of the question rather than as a step you complete first. You upload both files, say what connects them, and it matches the rows, reports the match rate, and continues straight into the analysis.

Step 1: Upload both files

Drop both workbooks into one workspace. Bloom reads Excel, CSV, TSV, and PDF, and auto-cleans as it ingests, so trailing spaces and mixed-case keys get handled rather than silently dropped.

You do not need to reorder columns so the key sits on the left, and you do not need the two files to share a layout.

Step 2: Describe the join in natural language

Say what connects them and what you want out. "Match the orders file to the customer file on email address, keep every customer even if they have no orders, and tell me how many failed to match" is a complete instruction.

Then keep going in the same breath, because this is the part lookups cannot do: "now show revenue by customer segment, and list the ten accounts with the largest drop against last quarter." The join and the analysis happen in one pass.

If this is a monthly routine, save it as an agent skill and re-run it on next month's files instead of retyping it.

Step 3: Export the joined result, chart, or deck

Take the joined table as a file, take the charts, or turn the whole canvas into a deck in one click — Professional, Business, or Fancy — and export it to PowerPoint or Notion.

That last option is the one that saves an afternoon. The join was never the deliverable.

Why this matters more than saving a formula

The comparison that counts is not formula-versus-no-formula. It is how each route behaves when the data misbehaves.

	VLOOKUP	XLOOKUP	Power Query Merge	Powerdrill Bloom
Key can sit anywhere	No	Yes	Yes	Yes
Survives an inserted column	No	Yes	Yes	Yes
Handles repeated keys properly	No	No	Yes	Yes
Isolates unmatched rows	Manual	Manual	Yes (anti join)	Yes
Cleans messy keys for you	No	No	Manual steps	Yes
Reports the match rate	No	No	No	Yes
Goes on to answer the question	No	No	No	Yes
Skill needed	Formula	Formula	Query editor	Natural language

Read that table honestly and the conclusion is not "Excel is obsolete". It is that Excel's tools are built to produce a joined table, and producing the joined table is the cheap half of the job.

Best practices when joining spreadsheets

Normalise the key before you match anything

Trim whitespace, force one case, and confirm that IDs are stored as the same data type on both sides. A join on a dirty key does not error — it just quietly under-matches, and a 60% match rate looks like a business finding rather than a data problem.

Always count the rows that did not match

The unmatched set is usually the most interesting output. Customers with no orders, orders with no customer record, SKUs that exist in one system and not the other: that is where the operational problems live. Our guide on merging data files walks through this in more depth.

Check the row count after the join, not before

If the left file had 4,000 rows and the joined result has 11,000, your key repeats and you have fanned out the data. That is fine if you intended it and a serious problem if you did not — especially before you sum a revenue column.

Decide on one-to-many before you aggregate

If one customer holds five orders, you either want five rows or one aggregated row. Summing revenue on the fanned-out version double-counts. This single mistake produces more wrong dashboards than any formula error.

Common mistakes to avoid

1. Joining on a name instead of an ID. "Acme Corp", "Acme Corp.", and "ACME Corporation" are three companies as far as any exact match is concerned.
2. Leaving VLOOKUP's fourth argument off. The default is approximate matching, which returns wrong values on unsorted data without raising an error.
3. Reading #N/A as zero. No match and a real zero mean opposite things, and wrapping everything in IFERROR(...,0) hides the difference.
4. Joining before deduplicating. If either side holds duplicate keys, the join multiplies them. Clean first, then join.
5. Summing after a one-to-many join. The classic double-count. Check your row count before you trust any total.

Conclusion

For a quick one-off pull where the key is clean, XLOOKUP is the right tool and takes thirty seconds. For a repeating join on stable files, build a Power Query Merge and use the anti join to catch what does not match. When the keys are messy, when the key repeats, or when what you actually need is the chart and the deck rather than the merged sheet, describe the join instead of writing it.

You can test that on your own two files at no cost — Powerdrill Bloom includes 1,000 daily refreshed credits on the free plan. The Excel AI assistant and merge CSV files pages show the same workflow, and analyzing Excel with AI covers the single-file version.

Frequently asked questions

What can I use instead of VLOOKUP to combine two Excel files?

XLOOKUP is the direct replacement and fixes VLOOKUP's biggest weaknesses: it searches in any direction, matches exactly by default, and does not break when a column is inserted. For a true join across two tables, Power Query's Merge is the better native tool because it handles repeated keys and can isolate unmatched rows.

Is Power Query better than VLOOKUP for joining files?

For anything repeatable, yes. Power Query performs a real join with selectable join kinds, refreshes when the source files change, and does not leave thousands of formulas in your workbook. VLOOKUP remains quicker for a single ad-hoc pull on one clean column.

How do I join two Excel files when the columns have different names?

Power Query lets you pick a different key column on each side, so the names do not need to match — only the values do. An AI data agent goes further and matches the columns while reading the files, then reports where the two sides disagree.

Why does my VLOOKUP return the wrong value instead of an error?

Almost always because the fourth argument was left off. VLOOKUP then performs an approximate match, which assumes sorted data and otherwise returns the closest lower value it can find. Set the last argument to FALSE to force an exact match.

Can I join two Excel files without formulas at all?

Yes. Power Query's Merge is a no-formula route inside Excel, though it uses the query editor. With an AI data agent you upload both files and describe the join in a sentence, which requires no formula and no query steps.