How to stop writing multiple vlookup formulas — use an excel sheet join instead
- Step 1Put main data in File A — File A is the sheet you would normally write VLOOKUPs on — the rows you want to keep. Only its first sheet is read.
- Step 2Put the lookup table in File B — File B holds the columns you were pulling. Every column except the key will be appended. First sheet only.
- Step 3Open the Sheet Joiner — Load the Multi-Sheet Joiner; drop File A in the first slot and File B in the second.
- Step 4Name the key in each file — Set
leftKeyto the main-data key header andrightKeyto the lookup key header. They need not share a name. - Step 5Choose left join — Keep
leftso every main-data row survives. Unmatched rows get blank lookup cells instead of #N/A. Useinnerto drop unmatched rows. - Step 6Run and replace your formulas — Download
joined-data.xlsx. The lookup columns are now plain values — delete the original VLOOKUP block.
VLOOKUP stack vs a single join
Why one join beats N formulas. 'Range drift' is the classic bug where inserting a column shifts every VLOOKUP's col_index_num.
| Concern | Multiple VLOOKUPs | Sheet Joiner |
|---|---|---|
| Columns pulled | One per formula (N formulas for N columns) | All File-B columns in one pass |
| Number vs text key | #N/A unless types match exactly | Matches — keys coerced to strings |
| Unmatched key | #N/A in every column | Blank cells (left) or row dropped (inner) |
| Maintenance | Re-point ranges on column insert (range drift) | Re-run the join; no formulas to fix |
| Output | Live formulas that recalc and can break | Static values, no recalculation |
Mapping VLOOKUP arguments to join options
If you think in VLOOKUP terms, here is the translation.
| VLOOKUP concept | Join equivalent |
|---|---|
lookup_value column | leftKey (the key header in File A) |
First column of table_array | rightKey (the key header in File B) |
col_index_num (which column to return) | Not needed — all File-B columns are returned |
FALSE (exact match) | Default behaviour — exact string match only |
#N/A on no match | Blank cells (left join) or dropped row (inner join) |
Tier limits
Replacing VLOOKUP needs two files (main + lookup), so Free (1 file) cannot run it.
| Tier | Max size / file | Max rows / file | Files |
|---|---|---|---|
| Free | 5 MB | 10,000 | 1 (cannot join) |
| Pro | 50 MB | 100,000 | 5 |
| Pro-media | 200 MB | 500,000 | 20 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
Side-by-side with the VLOOKUPs you are deleting. Keys match as strings, so the number/text gotcha that produces #N/A is gone.
Six VLOOKUPs become one join
You were pulling name, category, price, supplier, weight, and country from a product table. A single join with key = sku brings them all over at once.
Old approach (6 formulas, repeated down 5,000 rows): =VLOOKUP(A2,Lookup!$A:$F,2,FALSE) =VLOOKUP(A2,Lookup!$A:$F,3,FALSE) =VLOOKUP(A2,Lookup!$A:$F,4,FALSE) =VLOOKUP(A2,Lookup!$A:$F,5,FALSE) =VLOOKUP(A2,Lookup!$A:$F,6,FALSE) ... (and breaks if a column is inserted) Sheet Joiner (leftKey=sku, rightKey=sku, left): sku,name,category,price,supplier,weight,country <- all in one pass
Blank cell instead of #N/A
Where VLOOKUP returns #N/A, a left join leaves the lookup columns blank — much easier to filter and total.
Main data: Lookup: sku,qty sku,price X1,5 X1,9.99 X2,3 (X2 missing) VLOOKUP price column: Left join price column: 9.99 9.99 #N/A (blank)
Number/text key that VLOOKUP rejects
Main data has numeric ids, the lookup stores them as text. VLOOKUP gives #N/A everywhere; the join matches.
Main: Lookup: id,region id,manager 10,East "10",Dana 11,West "11",Lee Left join (id <-> id): id,region,manager 10,East,Dana 11,West,Lee
Inner join to drop unmatched rows
Switch to inner when you only want rows that have a lookup match — equivalent to filtering out the #N/A rows after a VLOOKUP.
joinType: inner Main X2 (no lookup) is removed: sku,qty,price X1,5,9.99
Lookup key column is not duplicated
Both files have a sku column. The join drops File B's sku (the key) automatically, so you do not get two sku columns.
Main: sku,qty Lookup: sku,price Output: sku,qty,price <- single sku, price appended
Edge cases and what actually happens
Lookup table has duplicate keys
Fan-outVLOOKUP silently returns the first match; a join returns every match, so a duplicate key in the lookup table fans the main row into multiple output rows. Deduplicate the lookup first with the deduplicator if you expected one row per key.
Number vs text key
PreservedKeys are coerced to strings before comparison, so 10 matches "10". This is precisely the mismatch that produces #N/A in VLOOKUP, and it joins here without any =TEXT() or =VALUE() wrapper.
Key header typed incorrectly
Invalid keyThe join reads the key by the exact header you enter in leftKey / rightKey. A typo or trailing space resolves to empty for every row, so a left join keeps all rows with blank lookup columns and an inner join returns nothing. Match the header text exactly.
Case-different keys
No matchString comparison is case-sensitive — SKU-1 and sku-1 will not join. Normalise case with the case converter before joining if your keys vary.
A lookup column shares a name with a main column
Lookup winsAny non-key column present in both files is overwritten by File B's value. If the main-data version should be kept, rename the lookup column first with the header rename tool.
Approximate-match VLOOKUP (TRUE)
Not supportedThis join is exact-match only — there is no banding/range-lookup equivalent of VLOOKUP(...,TRUE). For tiered or bracketed lookups, keep a formula or pre-bin the key into exact buckets before joining.
Lookup table on sheet 2
Sheet 1 onlyOnly the first sheet of each file is parsed. A workbook whose lookup table is not on tab 1 will be read wrong. Move it to the first sheet or export it on its own.
Free tier
Run rejectedReplacing VLOOKUP needs a main file and a lookup file — two files — which exceeds Free's 1-file limit, and the tool is Pro-minimum. Run 'Sheet Joiner requires Pro tier.' resolves with a Pro upgrade.
Whitespace around keys
No matchA trailing space makes "X1 " differ from "X1". Trim both files first with the whitespace trimmer so padded keys do not silently fail to match (the same way they would silently fail in VLOOKUP).
Result has more rows than the main file
ExpectedUnlike VLOOKUP (one return per row), a join fans out on duplicate lookup keys, so output rows can exceed main-data rows. The metrics panel reports the counts; dedupe the lookup if you wanted a strict one-to-one.
Frequently asked questions
How does one join replace six VLOOKUPs?
A join appends every column from the lookup file (File B) to the matching main-data row in a single operation. Where you had six VLOOKUPs each returning one column, the join returns all six columns at once — and as static values, so there is nothing to drag-fill or re-point.
What if the key column has different names in each file?
Set leftKey to the main file's key header and rightKey to the lookup file's key header. They can be named differently — only the values must match.
Does it handle keys with mixed types (numbers vs text-stored numbers)?
Yes. Keys are compared after String() coercion, so 123 and "123" match. This is exactly the case that returns #N/A in VLOOKUP, and it joins here with no helper column.
What replaces #N/A when there is no match?
On a left join, unmatched rows are kept with blank cells under the lookup columns — cleaner than #N/A and easy to filter. On an inner join, unmatched rows are dropped entirely.
Can it do an approximate-match (range) lookup like VLOOKUP(...,TRUE)?
No. The join is exact-match only. For tiered/bracketed lookups, pre-bin the key into exact buckets first or keep a formula for that one column.
What happens if the lookup table has duplicate keys?
VLOOKUP returns only the first match; the join returns all of them, fanning the main row into several rows. If you want one row per key, deduplicate the lookup with the deduplicator before joining.
Will I get two key columns in the output?
No. File B's key column is automatically dropped, so the key appears once. Other columns shared between the files are overwritten by File B's value.
How is this better than Power Query Merge?
Power Query Merge needs Excel for Desktop, a query editor, and a refresh step. This runs in any browser — Chromebook, iPad, Linux — with no install and produces a plain values-only file.
Are the output columns formulas or values?
Values. The merged .xlsx (joined-data.xlsx, sheet Joined) contains no VLOOKUP and no live links, so it never recalculates or breaks when the source files change. Re-run the join to refresh.
Why might the output have more rows than my main sheet?
Because a join fans out on duplicate lookup keys (VLOOKUP would have returned just the first). The metrics panel shows input vs output row counts; deduplicate the lookup file if you expected a one-to-one result.
What tier and size limits apply?
Pro minimum (the two-file requirement exceeds Free's 1-file cap). Pro covers 50 MB and 100,000 rows per file; Pro-media 200 MB / 500,000; Developer 500 MB / unlimited.
Do I need to install anything or upload my data?
No. Everything runs in the browser via SheetJS — your main and lookup files never leave the tab, and there is no add-in or desktop app to install.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.