Skip to main content

How I map status values between two apps with a Formatter Lookup Table (and why the fallback skips blank cells)

  • Nov 18, 2026 8:56 PM UTC
  • 0 replies
  • 1 view

Huzaifa_ZP

When I build a Zap that syncs or compares a status between two apps, the first thing that breaks is wording. The sheet says "In progress", the project tool says "In Progress" or "in_progress", and a Filter set to "does not exactly match" flags rows that are actually fine.

This is the setup I now put in front of every status compare or update step.

1. Pick one canonical set of values
I write down the statuses the business actually uses, lowercase with underscores: not_started, in_progress, blocked, done. Both apps get translated into this set, and only then compared.

2. Normalize the raw text
Formatter by Zapier > Text > Trim Whitespace, then Text > Lowercase. The Key column in a Lookup Table is case sensitive, so "Done" and "done" are different keys. Normalizing first means one row per spelling instead of one per capitalization.

3. Formatter > Utilities > Lookup Table
Lookup Key: the normalized value from step 2. The table looks like this:

  • in progress → in_progress
  • in_progress → in_progress
  • wip → in_progress
  • complete → done
  • completed → done
  • done → done

Fallback Value: UNMAPPED

I run one of these per app, so both sides land in the same set.

4. Catch new values instead of guessing
Right after the lookups I add Paths. If either value is UNMAPPED, one branch sends me a Slack message with the raw value and does nothing else. The other branch carries on. Sooner or later someone adds a status like "On hold", and this turns a wrong update into a one line fix in the table.

5. Compare canonical to canonical
Only now does the "does not exactly match" Filter run, and it compares the two lookup outputs, never the raw fields.

The gotcha: the fallback does not cover blanks
If the Lookup Key is empty when the Zap runs, the table returns no match and no value. The Fallback Value is only used when there is a value that matches nothing. So an empty status cell slips past an UNMAPPED check with an empty output. I put a Filter before the lookup (Status exists), or give blanks their own branch if blank really means "not started" in that team's sheet.

Two smaller things

  • If the destination field is a dropdown, its three dot menu has "Add lookup table step". Zapier builds the table from the dropdown's choices, which helps when the field expects internal IDs rather than the labels people type (HubSpot deal stages are a good example).
  • Formatter, Filter and Paths steps don't count toward tasks, so the extra steps don't change what a run costs.

I build sync Zaps like this for small teams, and this is the pattern I reuse the most. Does anyone keep the mapping in Zapier Tables instead, so teammates can edit it without opening the Zap?  .