Matching two CSV exports without a database

Two files arrive from two systems. One has the orders, the other has the refunds. Both name the customer, and the question you actually have is about neither file on its own.

This is a join. It is a join whether you do it with a formula, a script, or SQL, and naming it that way is useful because the problems are the same in all three.

What the lookup formula is really doing

The spreadsheet answer is a lookup. Point it at the key, point it at the other range, name the column you want, and drag it down.

It works, and it has three costs that are easy to miss.

It does one column at a time. Pulling four columns from the other file means four formulas, four ranges to keep in step, and four things to fix when a column moves. A join brings back every column you asked for in one pass.

It hides the failures. A key with no match produces an error value, and the usual response is to wrap the whole thing in something that turns errors into blanks. That is reasonable presentation and terrible bookkeeping, because a row that matched nothing now looks exactly like a row that matched something blank. The count of failures is the most interesting number in the whole exercise and it has just been erased.

And it takes the first match without saying so. If the other file has two rows for the same key, a lookup quietly picks one. A join returns both, which is sometimes surprising and always honest, because two matching rows is a fact about your data rather than a detail to be resolved by whichever happens to sort first.

The trap is the key

Almost every join that fails on real exports fails for the same reason, and it is not the join.

The key looks equal and is not. A trailing space survives every export and is invisible in every viewer. A customer number that one system stores as 00412 and the other as 412 is the same customer to a person and two different strings to any exact match. An accented name can be stored as one character or as a letter plus a separate accent mark, which render identically and compare as different.

So before matching, normalise both sides. Trim the whitespace, decide whether the key is text or a number and make both files agree, and strip accents if the key is a name. This is dull work and it is the work; the join itself is one line.

The check afterwards is worth doing every time. Count the rows in the first file, count the rows that matched, and look at the difference. If it is zero, be slightly suspicious rather than pleased, because a perfect match on data from two systems is unusual enough to be worth confirming.

There is a second kind of key problem that survives all of that normalising, and it is worth knowing about because the symptom is the opposite. If the result has more rows than the file you started with, the key is not unique on the other side. Two rows there for one row here produces two rows out, and the totals afterwards are then quietly doubled for those customers. Counting the distinct values of the key in each file before joining takes one query and answers this before it becomes a number in a report.

The other habit worth forming is to keep the raw exports. Normalising is easier to redo than to undo, and a key column that has been trimmed, padded and case-folded in place is a column whose original spelling is gone. Do the cleaning as part of the query rather than by editing the file, and next month the same query runs against a fresh export without any preparation at all.

When SQL is the shorter road

For one column against a small table, a lookup is fine and faster to write.

The balance tips when the question grows. Several columns from the other file, a filter on the result, a total per category, or the rows in one file that are missing from the other are all one query and several awkward steps in a spreadsheet. A query also reads as the question you asked, which matters three months later when you have to run it again and cannot remember which of the four formula columns was the important one.

Two exports loaded as two tables, matched on the shared column, is the whole job. Add a filter and you have the refunds over a threshold. Change the join to a left join and the rows that matched nothing appear with empty values instead of vanishing, which is the question the lookup formula answered so badly.

The other advantage is that the query is a record. It can be pasted into a message, kept in a note, and run again next month against the same two reports, which is not true of a formula that lives inside a cell reference that has since moved.

Keeping it off other people’s servers

Both files here are usually the sensitive kind. Customer names, order values, email addresses, sometimes more.

That makes the usual advice about uploading them to a converter worth ignoring. A join can run entirely in a browser tab with a database compiled to WebAssembly, which means the files stay on the machine they are already on and the only network request is for the engine itself. Whether a tool works that way is checkable rather than a matter of trust: open the network panel and run the query.