VLOOKUP vs Power Query vs SQL: which should you use to join sheets?
By the Analistable team · Updated · 5 min read
Use VLOOKUP (or XLOOKUP) for a one-off join of a few thousand rows where you need one or two columns from another sheet. Use Power Query when the same join must be refreshed every week or month, or when you need left, inner, anti or full joins. Use SQL when files are large, there are several tables, or you want a join you can read and reuse anywhere. Size, repeatability and skill decide it.
Part of our guide: How to join spreadsheets on a common column
Decision table
| Factor | VLOOKUP / XLOOKUP | Power Query | SQL |
|---|---|---|---|
| Best data size | Up to tens of thousands of rows | Up to around a million rows, varies | Millions of rows |
| Repeat monthly | Re-paste and re-fill | Click Refresh | Re-run the query |
| Join types | Left lookup, first match only | Left, right, inner, full, anti | All, plus any condition |
| Multiple matches | Returns the first | Expands to every match | Returns every match |
| Learning curve | Low | Medium | Medium to high |
| Result lives in | Formulas in the sheet | A refreshable table | A query result |
If you are torn between the lookup functions themselves, VLOOKUP vs XLOOKUP vs INDEX MATCH covers that choice; on this page “VLOOKUP” stands for the whole family.
VLOOKUP: fast for a one-off
A lookup formula is the quickest way to bring a column across. =XLOOKUP(A2, Customers!A:A, Customers!C:C, "Not found") fetches a region for each order. It is visible, needs no new tools, and anyone who uses Excel can follow it.
- It returns only the first match, so duplicate keys on the lookup side are silently ignored.
- Plain VLOOKUP defaults to approximate match; always pass FALSE (or 0) as the fourth argument for an exact match.
- Thousands of lookup formulas over whole columns can make a workbook slow to recalculate.
- Repeating next month means pasting new data and checking the formulas still cover every row.
Power Query: the repeatable middle ground
Power Query (Get & Transform in Excel, the same engine in Power BI) joins with Merge Queries. You pick both tables, click the key columns and choose a join kind. The steps are recorded, so next month you replace the source files and click Refresh. It also handles duplicates correctly by expanding every match, which is both a feature and a trap if you expected one row per key.
It needs a little learning and lives inside Excel or Power BI, so colleagues on Google Sheets cannot use it. Our article on Power Query join kinds explains each option, and Power Query vs Analistable compares it with a browser approach.
SQL: for size, many tables and reuse
SQL states the join in one readable sentence and runs comfortably on millions of rows. It can join on several columns, on ranges (“order date between start and end date”) and across four or five tables in one query. The barrier is running it: you need a database or an engine that reads files, such as DuckDB, SQLite or a database your company already has.
SELECT o.order_id, o.total, c.region
FROM orders o
LEFT JOIN customers c
ON c.customer_id = o.customer_id;Analistable takes away the setup: it loads sheets into DuckDB in your browser and writes this kind of SQL from a plain-English question, which suits people who can read SQL but would rather not type it. For writing it yourself, see how to join multiple tables in SQL.
Worked example: the same join three ways
Orders (5 rows) need each customer's region from a customer list. Customer C3 appears twice in the customer list by mistake, and order 1005 has a customer missing from it.
| Method | Rows returned | C3 order | Order 1005 |
|---|---|---|---|
| XLOOKUP | 5 | First region only | “Not found” |
| Power Query left join | 6 | Duplicated (2 rows) | Region null |
| SQL LEFT JOIN | 6 | Duplicated (2 rows) | Region NULL |
| SQL INNER JOIN | 5 | Duplicated (2 rows) | Dropped |
The lookup hides the duplicate; the joins expose it by adding a row, which would double-count that order's revenue if you summed totals. Neither behaviour is wrong, but you must know which one you are getting. Before any join, count distinct keys on the lookup side: if the count is less than the row count, you have duplicates to resolve.
Skill and handover
The best method is often the one the next person can maintain. A lookup formula is understood by almost everyone who opens the workbook. A Power Query step list is understandable to anyone willing to open the editor, and each step has a name you can rename to say what it does. SQL is the most compact and portable description of a join: the same query works in DuckDB, PostgreSQL, SQL Server or BigQuery with little change, but it is only readable to people who know SQL.
| Method | Typical maintainer | Where the logic is documented |
|---|---|---|
| XLOOKUP | Any Excel user | In each formula cell |
| Power Query | Excel power user, Power BI user | Applied Steps pane |
| SQL | Analyst or engineer | The query text |
If the join feeds a monthly report that a non-specialist will run, prefer Power Query over a SQL script they cannot edit. If it feeds an analyst's own work across large exports, SQL saves hours.
Performance in practice
Lookup formulas recalculate whenever something they depend on changes, so 50,000 XLOOKUPs over whole-column references can make every edit lag. Converting ranges to Excel tables and referencing exact columns helps, as does pasting values once the lookup is final. Power Query does the work only when you refresh, so day-to-day editing stays fast, though a refresh over several large files can take a minute or more. SQL engines such as DuckDB are built for this and join millions of rows in seconds on a laptop.
Rules of thumb
- Under ~10,000 rows, one-off, one or two columns needed: XLOOKUP.
- Refreshed regularly, or you need anti or full joins: Power Query.
- Large files, several tables or a join on conditions: SQL.
- Data split between Excel and Google Sheets: SQL via a tool that reads both, or the free combine sheets and Excel tool.
The join spreadsheets hub has step-by-step guides for each method.
Frequently asked questions
- Is Power Query faster than VLOOKUP?
- For large tables, usually yes, because the merge runs once on refresh rather than recalculating thousands of formulas. For small one-off lookups the difference is negligible.
- Can VLOOKUP do an inner join?
- Not directly. Look up every row, then filter out the “Not found” results; that leaves the rows that matched, which is equivalent to an inner join when keys are unique.
- Do I need a database to use SQL on spreadsheets?
- Not a server. Engines like DuckDB can query CSV and Excel files directly on your computer, and browser tools can run it without installing anything.
- Can I use SQL on Google Sheets?
- Google Sheets' QUERY function uses a SQL-like language but cannot join two ranges. For real joins, export to a database or use a tool that loads sheets into a SQL engine.
- Which method handles case and spaces in keys best?
- None automatically. Trim and standardise case first: TRIM and LOWER in Excel, Text.Trim and Text.Lower in Power Query, or TRIM and LOWER in SQL, then join on the cleaned column.
- Why did my row count go up after a merge?
- The key is duplicated on one side, so each match produces a row. Remove or aggregate the duplicates before joining if you expect one row per key.