VLOOKUP for TSV Files
The spreadsheet operation, without the spreadsheet: you have a main table with a code in it, a reference table that says what the code means, and you want the meaning attached to every row. Paste both, name the key on each side, and the columns you asked for are appended to the main table. Rows that find no match keep their place and get whatever default you set, so the file never changes length.
How to use
- Paste the main table on the left and the reference table on the right. Both need header rows.
- Name the key column in the main table. If the lookup table calls it the same thing, leave the second box blank.
- Say which columns to return, comma-separated. Blank returns every column of the lookup table except its key.
- Set a default such as
unknownor0so misses are visible rather than blank. - Read the match count. "412 of 500 matched" is the number that tells you whether the lookup was the one you meant.
Why the row count never changes
This is the difference between a lookup and a join, and it's the reason both exist here. A left join with duplicate keys in the second file multiplies rows: a key appearing three times in the lookup table produces three output rows. A lookup returns exactly one value per row, always, so the output has precisely as many rows as the main table did. When you're enriching a report that someone is going to reconcile against a total, that guarantee matters more than completeness.
The duplicate keys selector is what makes that guarantee possible. First and last pick one row from the lookup table and discard the rest — which mirrors what Excel's VLOOKUP does, silently. Join them concatenates the competing values with |, so the ambiguity is visible in the cell instead of hidden. The status line reports how many duplicate keys the lookup table had, so you know whether the choice mattered at all.
When a lookup fails
Comparison is literal, after the optional trim and case fold. The usual causes of a low match count, in the order they occur in real files: trailing spaces on one side, case differences, leading zeros stripped by a spreadsheet on one side only (00123 versus 123), and a number formatted as text on one side (1000 versus 1,000). The first two are the checkboxes; for the last two, run the offending column through clean numbers on both files so they normalise the same way.
If a returned column has the same name as one already in the main table, it's suffixed with _lookup so nothing is overwritten.
FAQ
Is there an approximate-match mode like VLOOKUP's TRUE?
No — matching is exact only. Approximate match requires a sorted lookup table and silently returns the nearest lower key, which is the source of a large share of spreadsheet errors. If you need banding — a score to a grade, an amount to a bracket — compute it with a calculated column instead, where the thresholds are written down.
Can I look up on more than one column?
Not directly — the key is a single column on each side. For a composite key, build one first with combine columns on both files using the same separator, then look up on the combined column.
What if I want the unmatched rows on their own?
Set the default to something distinctive and filter for it, or use set operations with A − B on the key column, which gives you exactly the rows that would miss.
Does the lookup table have to be sorted?
No. It's indexed by key into a hash map, so order is irrelevant and lookup time doesn't grow with the table's size. Only the memory does.
Privacy
100% client-side. Neither table is uploaded. See the privacy policy.