Lookup in Table searches another table for rows that match a value in this table. Its Return Type decides what each cell gets back: the first matching row, up to 100 matching rows, or the number of matching rows.
How do I set up the lookup?
In the video, Lookup in Table starts at 1:25.
In your table, click Add column, then Add enrichment, and search for Lookup.
Pick Lookup in Table. It says "Pull a value from another table" and is marked Free.
Set Table to Search to the table you want to look in.
Set Target Column to the column in that table to match against.
Set Filter Operator to Equals or Contains.
Set Return Type to First match, Up to 100 matches or Match count.
In Value to look up, press / and pick a column from this table, or type a value.
The Return Type list has three options: First match, Up to 100 matches and Match count.
The info icon next to Return Type says: "Return the first matching row, up to 100 matching rows, or the total number of matches."
For a step-by-step example with First match, see Send rows to another table, or look up data from one.
What does each Return Type give me?
First match gives you one row. The cell says Record Found and how many fields that row has, for example Record Found (14 fields).
Up to 100 matches gives you a list of the matching rows. The cell says how many it found, for example 9 Records Found.
Match count gives you the number of matching rows. Its cell also says, for example, 9 Records Found, but it holds the count, not the rows.
In the table, an Up to 100 matches cell and a Match count cell look the same. To tell them apart, open the cell's details: Up to 100 matches shows Return Type multiple and a list of Rows, and Match count shows Return Type count and no rows.
In this example, each company is matched on Size in another table of companies. Rows in the 201-500 band find 9 companies there, and rows in the 51-200 band find 16. The first and last columns are named after their Return Type. Rows > Company and Count were made from the first column, as shown further down.
What is inside a cell with several matches?
Select the cell and press Enter. For Up to 100 matches, the details show Return Type multiple, Count with the number of matches, and Rows with the matching rows, numbered from 0. Click the arrow next to a row to see its fields.
For Match count, the details show Return Type count and Count, plus a few other fields, but no Rows.
How do I put the match count, or a field from a match, into its own column?
Each click on Add to column gives you one column: the match count, or one field from one matched row.
Select the cell and press Enter to open its details. To reach a field inside a matched row, click the arrow next to the row number first.
Hover a field and click Add to column.
Count gives you a column with the number of matches on each row, shown as a plain number.
A field inside a matched row, such as Company under row 0, gives you a column named after it, for example Rows > Company. It shows that field from that one row only. Row 0 is the first match, so in this example the column shows one company per row (bolt or 12twenty), not all 9 or 16 matches.
These columns fill in when the lookup column runs.
To see every match, open the cell's details and look under Rows.
How do I count the matches?
Set Return Type to Match count.
Run the column. Each cell says how many rows matched, for example 9 Records Found. Open the cell's details to see the number as Count.
To get just the number in its own column, use a column set to Up to 100 matches: open the details of one of its cells, hover Count and click Add to column, as shown above.
What do Equals and Contains do?
Equals finds rows where the Target Column holds the same value, for example the same Size band.
Contains finds rows where the Target Column contains the value. For example, a Description that contains the text platform.
Value to look up can be a column from this table, or text you type, like platform.
What do I see when nothing matches?
With Match count, the cell shows a cross and 0 Records Found. In this picture, the Contains platform cell found 6 matching rows in the other table, and the No match column looked for text that no row contains.
With First match, a row with no match says No Record Found. See What a cell's message means.
Does a lookup cost anything?
Lookup in Table is marked Free. When you run the column from its header, every option in the Run column dialog says "Spends nothing from your wallet".
Good to know
Run 10 empty rows in the Run column dialog is there to test your setup first: "Pick this to test if your setup works".
When you add the column, the main button says how many rows it runs, for example Add column and run 10 rows. The arrow next to it offers Add column and run all 25 rows in this table and Add column without running.
To pull one value from a single matching row, see Send rows to another table, or look up data from one.