Shape · Combine tables

Lookup

Adds columns from a reference table to every row by matching one key, like Excel's VLOOKUP. Rows without a match stay, with blanks.

Also known as: VLOOKUP, XLOOKUP, find and replace

How it works

Wire the table to enrich into left and the reference table into right, then pick the Left key and Right key. Under Columns to bring in choose the reference columns to add, or leave it empty for every non-key column of the reference. Every left row is kept; a row with no match gets nulls in the added columns, and the row count never changes. When both keys share a name the key appears once. If a key value appears more than once in the reference table the run stops with an error naming it, so a dirty code list cannot multiply your rows; null keys never match. For anything else, such as range keys or inner joins, use Join.

Inputs and outputs

Settings

{
  leftKey: string
  rightKey: string
  pickColumns: string[]
}

Example

Order lines carry a sku and the product master has sku, name, category and cost. Set both keys to sku and bring in name and category.

More in Combine tables

• Join — Matches rows from two tables on one or more key columns: inner, left, right, full, anti or semi. Keys can also compare with <, > or between, for range joins.
• Union — Stacks the rows of two or more tables one under another. Columns line up by name or by position.
• Zip — Puts two tables side by side, row 1 with row 1, row 2 with row 2, with no key needed. Both tables must have the same number of rows.
• Cross Join — Pairs every row of one table with every row of the other, so 10 × 5 rows gives 50. For example, every store with every month.
• As-of Join — Matches each row to the closest row in another table by a sorted key such as a time. For example, attach the price in effect at the time of each trade.
• Fuzzy Match — Joins two tables on keys that are similar but not identical, such as customer names with typos. A score column shows how close each match is.