How to use
- Open the file to fill in (the left one) as an
.xlsxor.csvfirst. This page opens with a VLOOKUP match step already added. - In the step card’s Second table, drop or paste the lookup file to take values from, and check its sheet, encoding and header setting.
- Pick the key column in the left table and the key column in the lookup table (columns holding the same values, such as an employee number or product code).
- Pick the columns to bring, then the join (keep all left rows / matched rows only) and how keys are compared.
- Read
Found N / not found Mand the keys not found in the step report, then download. If keys are missing, fix the lookup table and match again.
Options
| Option | Choices | What it does |
|---|---|---|
| Join | Keep all left rows | Every left row stays; if nothing matches the new cells are blank (or the text you set) |
| Matched rows only | Rows without a match are dropped (inner join) | |
| Key match | Exactly equal | The text must be identical (E3 ≠ e3) |
| Ignore spaces and case | " E3" also matches e3 |
|
| If the key repeats | First / last match | Use only the first (or last) matching row of the lookup table |
| All matches | The left row is copied for every match, so rows increase (the report says by how many) | |
| Not found | Text to fill | On keep-all-left, fills the brought columns of unmatched rows with text such as - |
Example
Add departments to a staff list (left).
| Left (id, name) | Lookup (id, dept, name) |
|---|---|
| E1, Kim | E1, Sales, K |
| E2, Lee | E2, Dev, L |
| E9, Zed | E2, Design, L2 |
| (blank), Nobody | e3, x, y |
Matching the left id to the lookup id and bringing dept (keep all left, first match) gives:
- E1 → Sales, E2 → Dev (E2 appears twice in the lookup table, first match wins), E9 → blank, and Nobody with an empty key → blank
- Report:
Found 2 / not found 2 · keys not found: E9 · 1 duplicate lookup key | used the first match - A row with an empty key always counts as not found but is not added to the list of keys not found.
- Switch to the last match and E2 gets Design; switch to all matches and E2 becomes two rows (Dev and Design) for 5 rows in total (
1 duplicate lookup key added 1 row). With matched rows only, just E1 and E2 remain. - If you also bring
name, the left table already has anamecolumn, so the new one is calledname (ref).
How this differs from Excel’s VLOOKUP
- The key does not have to be the first column of the lookup range, and you choose columns by name, not by index. You can bring several columns at once.
- Only exact match is supported (no approximate
TRUEmode), but you can ignore spaces and case. - Instead of
#N/Ayou get a blank or your own text, and the keys not found are listed (first 50, thenand N more). - When the lookup key repeats the function returns the first value; here you can choose first, last or all.
- A number cell
1and the text"1"count as the same key, but007and7are different text and so different keys. If leading zeros were lost in one file, fix them first with Keep Leading Zeros.
Good to know
- The second table also gets automatic encoding detection for CSV (UTF-8, CP949) and values are not converted to numbers.
- Brought columns are added at the end of the left table, and values such as dates come over as read from the lookup table.
- You can stack dedupe, sort or pivot steps after it and save once.
Related tools
- Find differences between two tables: Compare Excel Files
- Stack several files vertically: Merge Excel & CSV Files
- Clean up repeated keys: Remove Duplicate Rows
- Stack several steps: Excel & CSV Workbench
Your files are processed only in your browser and are never sent to a server.