---
title: "Label rows with a lookup table in Strata | Oplema"
description: "Learn to label rows from a small mapping table with Manual Input and Lookup in Strata, and make an unlisted code show up instead of staying blank."
url: "https://oplema.com/learn/many-sources/label-rows-from-a-lookup-table"
updated: "2026-11-04"
---

Lesson 4 of 9 · Lab

# Label rows from a small lookup table

Lookup copies values from a small reference table onto every row whose key matches. Every row stays, matched or not, so a code missing from the table leaves a blank label and nothing warns you.

Manual Input is a table you type into the flow, for short lists that live in someone's head, like a set of labels.

Marit, who owns [fictional Kramerica Bikes](https://oplema.com/learn/export-to-report/first-data-flow), doesn't read bike codes. She dictates five labels: `CTY` City bike, `EBK` E-bike, `KID` Kids' bike, `CRG` Cargo bike and `TDM` Tandem. The lower **Manual Input** has five empty rows for them, and **Lookup** matches each booking's `bike_code`.

You use Lookup rather than Join here. Why?

There's one label per code, and every booking must stay, labeled or not. A Join would drop unmatched bookings to its Left output.

## Type the labels and find the code nobody listed

1. In the live [Strata](https://oplema.com/strata) flow below, Oplema's browser app for data prep, **Lookup** and the two blocks after it are red: Lookup has no table yet. Drag from the lower **Manual Input** to **Lookup**'s lower left dot. Then set Lookup's **Lookup Column** to `code`.
2. Select the lower **Manual Input**. Click the first empty cell under `code`, type `CTY`, press Tab and type `City bike`.
3. Fill the next four rows the same way, clicking each row's first cell: `EBK` E-bike, `KID` Kids' bike, `CRG` Cargo bike and `TDM` Tandem. Then select the block.
4. Select **Summarize**, the last block: 26 bookings have an empty `bike_type`. Scroll **Lookup**'s preview to a row with no `label`: its `bike_code` is `GRV`, which isn't on Marit's list.
5. Make **Formula** turn a missing label into `Unlisted`, so the next unknown code shows up. Check: **Summarize** shows 26 `Unlisted`. If you're stuck
   Select **Formula**. In **Expression**, replace `label` with `COALESCE(label, 'Unlisted')`, then select the block. COALESCE uses the first value that isn't empty; it's under **Functions › Null handling**. Its **Output type** stays VARCHAR, which is text.
6. Marit says `GRV` is the new gravel bike. In the lower **Manual Input**, hover under the last row and select **+**. Click the new row's first cell, type `GRV`, press Tab and type `Gravel bike`.
7. Select **Summarize**. `Unlisted` has gone, and Gravel bike has 26 bookings.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What Summarize should show

Before Gravel: City bike 494, E-bike 306, Kids' bike 159, Tandem 155, Cargo bike 78 and `Unlisted` 26. Lookup's preview keeps all 1,218 rows throughout.

## Make the unknown visible

The 26 bookings (€1,239.00 with VAT) are all at Station Road from September 16. The row count didn't change, so nothing warned you. `COALESCE` turns the next unknown code into `Unlisted`, which you'll notice in a chart, where a blank would disappear.

Keep the `COALESCE` after adding Gravel: delete the Gravel row, and the 26 come back as `Unlisted`.

Your line for Marit: "Every booking has a bike type now. Station Road has been hiring gravel bikes since September 16, 26 bookings so far."

See the [Lookup](https://oplema.com/docs/strata/combine/lookup), [Manual Input](https://oplema.com/docs/strata/io/manual-input) and [Formula references](https://oplema.com/docs/strata/prep/formula). For later: [Star schemas](https://oplema.com/learn/business-intelligence/star-schemas) shows where labels like these belong.

## Common lookup mistakes

- **Counting rows to check a lookup.** Lookup never adds or drops rows. Count the blanks instead.
- **Two rows for one code.** Lookup takes the first match and ignores the rest. Keep one row per code.
- **Leaving blanks for a chart to hide.** A blank label drops out of a chart's legend. Fill it with a word you'll see.

## Lookups in SQL and Excel

```
SELECT  b. * ,  COALESCE ( t . label ,  'Unlisted' )  AS  bike_type
FROM  bookings b
LEFT JOIN  bike_types t  ON  upper ( b . bike_code )  =  upper ( t . code );
```

A left join works as a lookup only with one row per code. Excel's `=XLOOKUP(F2, Types!A:A, Types!B:B, "Unlisted")` does the same, and its fourth argument plays `COALESCE`.
