sqlOnCsv running a LEFT JOIN that lists the orders whose customer isn't on the Customers sheet

Most small businesses run on a workbook. A Customers sheet, an Orders sheet, a Totals sheet that pulls them together with VLOOKUP and SUMIFS. It works, until it doesn't: three people have their own copy (orders_final_v3_ANNA.xlsx), the file takes a minute to recalculate, somebody sorts one column and not the others, and the monthly total no longer matches the bank.

This is for the person who owns that workbook, or the developer who's been asked to "put it in a proper database". We built the small version of it, with the kinds of mess a real shared sheet collects, and looked at what the formulas say next to what a database says about the same data.

The workbook

Eight customers, ten orders. The Customers sheet was built the way shared sheets are: new rows added at the bottom, never sorted. Harbor Coffee changed its name to Harbor Coffee Ltd and got a better discount, and someone added it again rather than editing the old row. On the Orders sheet, one customer ID was typed with a trailing space, and two orders belong to customers nobody added yet.

text
Customers                                   Orders
customer_id  name                discount   order_no  customer_id  amount
C104         Bloom Florist        5%        A-1001    C101         120.00
C101         Harbor Coffee        0%        A-1002    C104          64.50
C107         Northside Gym       10%        A-1003    C107         310.00
C102         Pine & Oak Joinery   0%        A-1004    C102         980.00
C110         Lumen Studio         5%        A-1005    "C103 "        45.00
C103         Fern Bakery         15%        A-1006    C109         220.00
C101         Harbor Coffee Ltd    5%        A-1007    C110          75.00
C108         Willow Dental        0%        A-1008    C108         410.00
                                            A-1009    C105          99.00
                                            A-1010    C101          35.00

On the Orders sheet, three lookups of the customer's name, and a Totals sheet with SUMIFS per customer. We had the workbook calculated by LibreOffice Calc 25.2, which implements these functions the way Microsoft documents them for Excel.

What the formulas said

text
order    customer_id  =VLOOKUP(C2,Customers!A2:D9,2)  =VLOOKUP(...,2,FALSE)  =XLOOKUP(...,"not found")
A-1001   C101         Harbor Coffee                   Harbor Coffee          Harbor Coffee
A-1002   C104         Bloom Florist                   Bloom Florist          Bloom Florist
A-1003   C107         Northside Gym                   Northside Gym          Northside Gym
A-1004   C102         Harbor Coffee         wrong     Pine & Oak Joinery     Pine & Oak Joinery
A-1005   "C103 "      Harbor Coffee         wrong     #N/A                   not found
A-1006   C109         Pine & Oak Joinery    wrong     #N/A                   not found
A-1007   C110         Lumen Studio                    Lumen Studio           Lumen Studio
A-1008   C108         Pine & Oak Joinery    wrong     Willow Dental          Willow Dental
A-1009   C105         Harbor Coffee         wrong     #N/A                   not found
A-1010   C101         Harbor Coffee                   Harbor Coffee          Harbor Coffee

The first column is the formula most people type, and half its answers are wrong. Without the fourth argument, VLOOKUP does an approximate match, which assumes the first column is sorted and jumps through it by halves. On an unsorted list it lands on whatever row the jumping ends at, and returns a real-looking name. Nothing turns red. Microsoft's own page says the first column must be sorted for approximate match; few people read that far.

The exact-match version (FALSE) and XLOOKUP, which matches exactly by default, get the five right and say plainly when a customer isn't there. But look at A-1001 and A-1010: both say Harbor Coffee, the old name, because lookups stop at the first match. The newer row with the 5% discount is never read. If the Totals sheet applied discounts, Harbor Coffee would be charged the old price, forever, and no cell would show it.

The Totals sheet was quieter still. SUMIFS per customer added up to 1,994.50; the ten orders add up to 2,358.50. The 364 missing are the order typed as "C103 " (45.00, so Fern Bakery shows 0), and the two orders for customers that aren't on the sheet (220.00 and 99.00), which no row of the Totals sheet asks about. The sum is wrong and looks finished.

The same data as two tables

A database table looks like a sheet: rows and named columns. The difference is that it holds one kind of thing, and you can tell it rules. Here that's two tables, customers and orders, with customer_id in both. The lookups become a JOIN:

sql
SELECT o.order_no, o.customer_id, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

 A-1001 | C101 | Harbor Coffee
 A-1001 | C101 | Harbor Coffee Ltd      <- the duplicate shows up as two rows
 A-1002 | C104 | Bloom Florist
 ...
 A-1010 | C101 | Harbor Coffee
 A-1010 | C101 | Harbor Coffee Ltd

A JOIN doesn't pick a first match; it returns every match. That looks worse, and it's better: the problem VLOOKUP hid is on the screen. The orders whose customer is missing, the ones that came back #N/A, are a LEFT JOIN with a check for no match:

sql
SELECT o.order_no, o.customer_id, o.amount
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

 A-1005 | 'C103 ' |  45
 A-1006 | 'C109'  | 220
 A-1009 | 'C105'  |  99

You don't need to install anything to try this. Save each sheet as "CSV UTF-8", drop both files into sqlOnCsv and run the query; it runs in the browser and the files aren't uploaded:

sqlOnCsv with customers.csv and orders.csv loaded as tables, running the LEFT JOIN: 3 rows, orders A-1005, A-1006 and A-1009 have no customer

The totals become a GROUP BY, which groups the rows that exist rather than looking up a list of customers someone typed. Every order lands in some group, so nothing falls through:

sql
SELECT trim(customer_id) AS customer_id, count(*) AS orders, sum(amount) AS total
FROM orders GROUP BY trim(customer_id) ORDER BY 1;

 C101 | 2 |  155.0
 C102 | 1 |  980.0
 C103 | 1 |   45.0     <- Fern Bakery, 0 on the Totals sheet
 C104 | 1 |   64.5
 C105 | 1 |   99.0     <- not on the Totals sheet at all
 C107 | 1 |  310.0
 C108 | 1 |  410.0
 C109 | 1 |  220.0     <- not on the Totals sheet at all
 C110 | 1 |   75.0     total 2,358.50

And the rule the sheet never had takes one line. Telling the database that a customer ID appears once:

text
CREATE UNIQUE INDEX customers_id ON customers (customer_id);
Error: UNIQUE constraint failed: customers.customer_id

It refuses, because Harbor Coffee is in there twice. Fix that row once (keep the newer one; removing duplicate rows covers how at any size), add the index, and nobody can add a second C101 again. A foreign key from orders.customer_id to customers does the same for orders: an order for C109 is refused until C109 exists. A spreadsheet's data validation dropdown is the closest thing it has, and it only checks what's typed into that one cell.

Formula to SQL, side by side

text
In the workbook                           In the database
VLOOKUP / XLOOKUP / INDEX-MATCH           JOIN
#N/A from a lookup                        LEFT JOIN ... WHERE right side IS NULL
SUMIFS, COUNTIFS, AVERAGEIFS              GROUP BY with SUM, COUNT, AVG
pivot table                               GROUP BY (or pivotTable, which also writes the SQL)
filter                                    WHERE
sort                                      ORDER BY (the stored rows never move)
Remove Duplicates                         DELETE with ROW_NUMBER(), then a UNIQUE index
data validation list                      FOREIGN KEY
TRIM, UPPER, LEFT                         trim(), upper(), substr()

What you give up, and what you keep

Be honest about the cost. Formulas that sit next to the data and update as you type become queries you run. Colours, comments in cells and "I'll just type a note in column K" have no place in a table. And a table wants one value per cell: C101, C104 in one cell has to become two rows somewhere.

You keep Excel, though. Excel can read straight from a database (Data → Get Data → From Database, or From ODBC for SQLite, PostgreSQL and MySQL drivers), so the Totals sheet can become a query result that refreshes instead of a pile of SUMIFS. People who only read the numbers don't have to change anything.

Moving it over

1. Save each sheet as "CSV UTF-8 (Comma delimited)", not plain CSV, or names with accents arrive broken (why). 2. Try your real questions in sqlOnCsv first; if the JOINs and GROUP BYs answer them, you know the shape works. 3. Get the CREATE TABLE from csvToSql, with types you check yourself (IDs and phone numbers as text, so leading zeros survive). 4. Start with SQLite: one file, no server, fine for a team that edits a few hundred rows a day; move to PostgreSQL when several people or an app write at the same time. 5. Add the unique keys and foreign keys, and let the database refuse the mistakes the sheet used to absorb.

When not to bother: one person, one sheet, no lookups between sheets, a few thousand rows. A spreadsheet is a fine tool for that. The time to move is when the same fact lives in two places, or when more than one person writes to it.

Load your sheets as CSV files and run JOINs and GROUP BYs on them, without a database. Free, in your browser.

Open sqlOnCsv

Once the data is in a database, Pilotbase gives you a grid to browse and edit it like a sheet, with a SQL editor next to it. It connects to SQLite, PostgreSQL, MySQL and more, and if you'd rather ask in plain words, its built-in agent writes the query for you (then check the number it gives you).

Open a SQLite file or a Postgres database and work with it like a spreadsheet. Pilotbase is free for Windows, macOS and Linux.

Get Pilotbase

Sources

Microsoft Support: VLOOKUP function (approximate match needs a sorted first column), XLOOKUP function, Excel specifications and limits (1,048,576 rows per sheet). SQLite: SELECT and joins, CREATE INDEX, foreign keys. The workbook was calculated with LibreOffice Calc 25.2 and the same data queried in SQLite 3.46, October 2026; the script that builds both is kept with the article's change request.