1. Home
  2. Spreadsheets & Data
  3. VLOOKUP Two Files

VLOOKUP Two Files

Match two spreadsheets on a shared column and pull values across, like VLOOKUP.

For every format and option in one place, use Excel & CSV Workbench →

Runs in your browser · No upload
The table opened in the workbench is the left one and the table you load below is the lookup table. Pick the key on each side and the columns to bring; keys not found are reported.

Operations

Look up rows in a second table by key and bring the columns you want. Reports keys not found.
Second table
Load the second table (file or paste).
Columns to bring
Files are processed in your browser and never sent to a server.

How to use

  1. Open the file to fill in (the left one) as an .xlsx or .csv first. This page opens with a VLOOKUP match step already added.
  2. 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.
  3. 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).
  4. Pick the columns to bring, then the join (keep all left rows / matched rows only) and how keys are compared.
  5. Read Found N / not found M and 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 a name column, so the new one is called name (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 TRUE mode), but you can ignore spaces and case.
  • Instead of #N/A you get a blank or your own text, and the keys not found are listed (first 50, then and N more).
  • When the lookup key repeats the function returns the first value; here you can choose first, last or all.
  • A number cell 1 and the text "1" count as the same key, but 007 and 7 are 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.

Your files are processed only in your browser and are never sent to a server.

Can't find the tool you need?

Tell us what you want to do. We build the most requested tools first.