Available on plans:
Free
Pro
Team
Enterprise
With a lookup table, you can automatically match data from one app with the corresponding data needed by another app. You can then use the value returned by the lookup table to set a custom value for dropdown menu fields.
ExampleIf your trigger app sends the customer's email but your action app needs a customer ID, you can create a lookup table to match the name to the correct ID, and use the result in an action step.
This article explains how to create a lookup table using either Zapier Tables or a Formatter step. Each app offers different features, so you can select what works best for your use case:
Use Zapier Tables if:
- You need to match the same value to multiple pieces of data, for different apps.
- You want to share access with teammates to maintain the lookup table.
Use Formatter if:
- The data you want to check comes as line items. Formatter can repeat the process for each line item in your data.
- You want to set a backup value in case the data received is not available in the lookup table.
Use a Formatter lookup table step
1. Add a Formatter step
In the Zap editor:
- In the Zap outline, click the Action step or the plus sign + to add a new action. A dialog box will open.
- In the search box, search for and select Formatter. The right sidebar will open to the Setup tab.
- Click the Action event dropdown menu and select Utilities.
- Click Continue.
2. Set up your transform
- Click the Transform dropdown menu and select Lookup Table.
- In the Lookup Key field, select a field from the trigger or a previous action step. The Zap will use this value to search the lookup table for the correct row.
- In the Lookup Table fields:
- Key: In the left column, enter the text for each key. This should match the data the Zap could receive from the trigger. This column is case-sensitive.
- Value: Enter the corresponding value for each key in this column. This is what the Zap will pass on to other steps, if it finds a matching key.
- Click + Add value set to create more key and value pairs.
- (optional) In the Fallback Value field, enter a value to be used if there is no matching key.
If your sales team inputs the customer name in a spreadsheet, but you need the customer ID to find them in your CRM app, you can add the customer's name to the Key column (for example, "Jane Doe"), and the corresponding ID to the Value column (for example, "12345"). You can then add a line for each customer so that the Zap can pass the correct value to other steps.
3. Test your step
- Click Test step. If the transformation is successful, the step will return the value associated with the key, or the fallback value (if you set one).
NoteIf the field you use as the lookup key is blank at the time the Zap runs, the lookup table will not return any matches. In that case, the Zap run will send no value, as the fallback value is only used when there are existing values but no matches.
Use Zapier Tables
When using Zapier Tables as a lookup table, you need to create at least two fields: a lookup key and the result you want to use in another app. You can also create more fields if you're using different values in different apps.
1. Create your table
You can start your table using the lookup table template. If you do, your table will have the following fields:
- Key: a field to enter the original values that you want to check, coming from a trigger or action app.
- Value 1, Value 2 and Value 3: you can fill these fields with the corresponding values you need.
TipYou can also start from a blank table. Learn more about Zapier Tables.
2. Customize the table
You can remove any of the value fields that come with the template by clicking the field name and then clicking Delete field. You can also add extra fields by clicking the + icon on the right side of the table.
3. Add the table as a step on your Zap
Once you have filled out the table, you can add it to your Zap. If you already have a Zap started, you can add an action to it. If you're creating a new Zap:
- From your table, click the Linked assets icon on the left sidebar. The Zaps tab will be displayed.
- Click + Create to start a new Zap.
- In the dialog box, click + Start from scratch.
- Click Start building your automation. A new window or tab will open with your Zap.
- In the Zap editor, click the Action step. A dialog box will open.
- In the search box, search for and select Zapier Tables. The right sidebar will open to the Setup tab.
- Click the Action event dropdown menu and select Find record.
- Click Continue.
4. Set up your step
- In the Table ID dropdown menu, select the table you created.
- In the Filter Count dropdown menu, leave it as 1.
- In Lookup Field 1 dropdown menu, select the field to search by. This should be the field that contains the information you receive from your trigger.
- Set up the trigger for your Zap.
5. Map the Zapier Tables output to another action step
Once you have your trigger and the Zapier Tables step ready, you can add a new action at the end of your Zap. This step will receive the corresponding value from the table.
- Follow the steps to add a new action.
- Once your app is connected, click the field that will receive the table value.
- In the dropdown menu, click the name of your Zapier Tables step.
- Select the name of the field you want to send to this app.
Once you’ve set your lookup table step, you can add other actions to use the result in your Zap.