Join
Lookup
Looks each row up in a reference table by one key and copies the matching columns across — a zip code becomes a city and state, a product code becomes its name and price.
When to use it
Use it instead of Join Tables when the reference table is a code list and every incoming row should come out exactly once, matched or not. Connect your rows to the Data pin and the reference table to the Reference pin.
In a pipeline
File InputLookupExport CSV
Settings
| Setting | Type | Required | Default or hint |
|---|---|---|---|
| Match on (this table) | select | Yes | Select column… |
| Match on (reference table) | select | Yes | Select column… |
| Columns to bring back | select | — | None picked: all reference columns are added |
| When there is no match | select | — | blank |
| Value for unmatched rows | text | — | e.g. NOT_FOUND |
Example
City and state from a zip code
- Match on (this table)=Zip
- Match on (reference table)=Zip
- Columns to bring back=City, State
- When there is no match=Leave the added columns blank
| Order | Zip |
|---|---|
| A-1041 | 10001 |
| A-1042 | 94105 |
| A-1043 | 73301 |
| Order | Zip | City | State |
|---|---|---|---|
| A-1041 | 10001 | New York | NY |
| A-1042 | 94105 | San Francisco | CA |
| A-1043 | 73301 | — | — |
The Reference pin carries Zip, City and State for 10001 (New York, NY) and 94105 (San Francisco, CA). 73301 is not in it, so its added columns are blank and the preview says 1 of 3 rows found no match.
Worth knowing
- A lookup never multiplies rows: if a key appears more than once in the reference table, its first row is used and the preview says how many repeats there were.
- Leave Columns to bring back empty to copy every reference column except its match column.
- Keys match as exact text, so 10001 and 010001 are different — trim or convert them first with Trim Whitespace or Type Conversion if needed.
- Choose Keep only matched rows to use the reference table as an allow-list.