Many teams build recurring reports by exporting data from two systems into Excel and matching rows with VLOOKUP. It works until it does not: the file becomes slow, a column moves and the formulas break, and nobody is sure the numbers are current. This post explains why the pattern is fragile and what to do instead.
How the VLOOKUP routine usually looks
- Export orders from the sales system to a file.
- Export customers (or invoices, or stock) from another system.
- Paste both into a workbook and use VLOOKUP or XLOOKUP to pull one sheet's columns onto the other.
- Fix the rows that came back as #N/A, then copy values into the report.
- Repeat next week, or next month.
Why it breaks
- Snapshots: the numbers are frozen at export time. By the time someone reads the report, they may already be wrong.
- Silent mismatches: a key with a trailing space or a different type (number versus text) returns #N/A, and approximate-match mode can return the wrong row without any warning.
- Duplicates: VLOOKUP returns only the first match, so a customer with two rows is under-counted.
- Fragile references: inserting a column shifts the index number in the formula.
- Process risk: the steps live in one person's head, and the file grows until it takes minutes to open.
The better pattern: join at the source
Instead of copying data out and matching it by hand, connect to the systems directly, choose the two tables, define the matching columns once, and let the tool do the join. The same definition then runs again whenever you need the report, so the work is repeatable rather than repeated.
Choosing the join type
VLOOKUP behaves like a left join: every row on the left stays, and matches are added where found. Make that choice deliberately:
- Inner join: keep only rows that exist on both sides. Use it when a missing match means the row should not appear.
- Left join: keep every left row and show empty values where there is no match. Use it to find gaps, for example orders with no customer record.
SELECT o.order_id, o.total, c.name, c.segment
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id;Checks to run on every new join
- Key columns have the same type and the same format on both sides.
- Row count after the join equals what you expect: more rows than before means duplicate keys, fewer means you used an inner join and lost rows.
- Spot-check five rows against the source systems.
- Filter for rows with no match and look at them before sending the report.
Doing it in Ruamhub
On the Join Canvas you drop a table from each connection, draw a line between the key columns, choose the join type, and preview the result before saving it as a pipeline. No SQL is required, and you can also add filters, group by and formula columns. The step-by-step version is in our guide to replacing the weekly VLOOKUP report.