Join Two Excel Workbooks, Without VLOOKUP
Customers in one workbook, orders in the other, and you want them together. In Excel that is a lookup across files that breaks the moment a row moves. Here it is a join — and you do not have to write it yourself.
The Spreadsheet Version of This Problem
The usual approach is a VLOOKUP or an INDEX/MATCH pointing at the other workbook, dragged down every row. It works, and it comes with a familiar set of costs:
- The link breaks if the other file is renamed, moved, or simply not open.
- Inserting a column shifts the index and the formula quietly returns the wrong field.
#N/Aappears for unmatched rows and you cannot easily tell which ones, or why.- Four hundred rows is fine. Forty thousand is a spinning cursor.
- Aggregating the joined result means a pivot table on top of the lookup, and now two things can break.
A join does the same work as one operation, against whole tables, and tells you what did not match.
Doing It
- Open the app and add both workbooks.
customers.xlsxbecomesCandorders.xlsxbecomesO— check the Alias column rather than assuming. - Pick the sheet for each file in the Sheet column if the workbook has more than one.
- Ask for what you want, in the plain-English box:
total amount by city produces this, with no join written by hand:
SELECT C.city, SUM(O.amount) AS total_amount FROM C INNER JOIN O ON C.customer_id = O.customer_id GROUP BY C.city
It found customer_id in both workbooks and joined on it. Not because the names matched — that alone is not evidence — but because the values in one column actually occur in the other. Both signals have to agree before a relationship is used.
Add both workbooks and ask for the total by city.
Open the tool →More of the Same
how many orders by region:
SELECT C.region, COUNT(*) AS count_orders FROM O INNER JOIN C ON O.customer_id = C.customer_id GROUP BY C.region
top 5 orders by amount, which only needs one of the two:
SELECT * FROM O ORDER BY amount DESC LIMIT 5
Four hundred orders against thirty-five customers, with no formula to drag down and nothing to break when a row moves.
Writing the Join Yourself
The generated SQL is editable, and often the fastest route is to let it write the first draft and then change it. The four join types behave as they do anywhere:
| Join | Keeps | Use it when |
|---|---|---|
INNER JOIN | Only rows that matched on both sides | You want the overlap and nothing else |
LEFT JOIN | Every row from the first table | You want all orders, matched or not |
RIGHT JOIN | Every row from the second table | Same idea, other way round |
FULL JOIN | Everything from both | Reconciling two lists that should agree |
The genuinely useful one is finding what did not match — the question #N/A never quite answers:
SELECT O.order_id, O.customer_id, O.amount FROM O LEFT JOIN C ON O.customer_id = C.customer_id WHERE C.customer_id IS NULL
That is every order whose customer is missing from the customer workbook, listed explicitly.
When a Join Returns Nothing
| Cause | Fix |
|---|---|
One side is text, the other a number — "1001" against 1001 | Very common with Excel. Compare them as text on both sides, or fix the column in the source. |
| Trailing spaces in one file | TRIM() both sides of the join condition. |
Different case: C004 against c004 | Text comparison here is case-exact. Use UPPER() on both sides. |
| You joined on the wrong pair of columns | Run SELECT * FROM C LIMIT 5 and the same for O, and look at what is actually in each key column. |
Reasonable Questions
Does this change my workbooks?
No. Files are read, never written. The original workbooks are untouched, and the result goes out through the Export menu as a new file.
Can I export the joined result back to Excel?
Yes — Export, then Excel. You get a new .xlsx of the result set, which you can send to whoever asked.
What about formulas in the source workbook?
They come through as their computed values. Formatting, colours and charts are not imported.
My header is on row three, not row one.
Set Skip Rows to 2 on that file's row in the File Select panel.
How big can the workbooks be?
50 MB and a million rows each. A join across two files of that size is real work for a laptop, but it is work that a spreadsheet would not attempt at all.
Are my spreadsheets uploaded?
No. Both are read in the browser tab. Here is how to confirm that yourself.