Don't be afraid of the lookup table

  • 10 November 2020
  • 6 replies
Don't be afraid of the lookup table
Userlevel 4
Badge +1

The lookup table is one of my favourite tools in the Zapier toolbox. With this function, we can easily take information that comes to us from one app and transform it into corresponding information that we can send to another app.

For example, let’s say you have an e-commerce store and you want to add each purchase to your accounting software. The problem you’re running into is that the SKUs you’ve set up in your store don’t match the product IDs in your accounting app. How do you make sure your purchases get recorded correctly?

This is where a lookup table comes in handy. By creating a table that matches each SKU with its corresponding product ID, we can send each purchase on to your accounting files without having to worry about missing information.

Think of the lookup table like the directory at your doctor’s office. You know your doctor’s name, but you don’t know which office they’re in. You head to the information desk and give your doctor’s name to the person there. They act like a lookup table, find your doctor, and give you their office number. Once you have that, you can head on to your appointment.

The anatomy of the lookup table


There are four pieces that make up a lookup table.

  1. The Lookup key. This is the information we get from your previous step — usually, but not always, your trigger step. 

  2. The Key (left column) can be one or many rows of options that could come in the Lookup Key step. When this step runs, we’ll match the information in the lookup key with the options in this list. Please note that the information in the lookup key and the information in the key column must match exactly.

  3. The Value (right column) is the corresponding information for each option in the key column. When we match one of those options, the table then outputs the information in the value column.

  4. The Fallback Value allows you to add default information should none of the listed options match.

Using the Lookup Table

Let’s look at an example workflow. You run a blog, and use Trello as your editorial calendar. You create a card for each potential blog post, and to indicate the author of each post, you assign a member in your Trello organization. 

Once the article is ready for publication, you move the card to your publish list on your Trello board and a Zap posts it on your WordPress site. 

But, when the details of that card come from Trello, each member has a long ID:


These IDs are unique to Trello, so they won’t work when we try to find the ID of the author in WordPress, which uses a completely different ID structure:


This is where the lookup table will come in handy. We can match the ID coming from Trello with the ID expected in WordPress:


When the Lookup table runs, it matches the key with the corresponding value and outputs the correct author ID in WordPress:


We can then use the output of the table in the author field in our WordPress step to make sure the correct author is assigned to the post:


Sending multiple items through the lookup table

Let’s make this a bit more complicated. If we take another look at our ecommerce example, what happens when your customers purchase more than one item in your store? This is definitely good for sales, but a bit more complicated when we want to send this through a Zap and on to your accounting software.

When an order comes through most ecommerce apps, Zapier receives the list of products as a line-item array. So instead of getting one product SKU, we get multiple in one list. Does this mean we need a separate Lookup Table for each product purchased? What if people buy fewer or more products than I added a lookup table step for?

The good news is the lookup table can work with that line-item array! So, no need for multiple steps and multiple tables. Just one that will give us everything.


When the Zap gets to this Formatter step, it will go through each item in that array and match it in the table. Once it’s done, it will create a brand new line-item array with the corresponding information on the table.


Most accounting programs also have line-item support, so when we send that outputted array from the lookup table, you won’t need to add any extra steps, they’ll be able to read it and add each of those items.


This means that all of your information moves through the Zap and on to the right places in the way each app needs it, without a lot of extra work on your part.

More things you can do with the Lookup Table

Matching a selection in a form with a specific tag or list

In this scenario, you have a form for your new leads where you ask them to choose which of your products they’re interested in so you can segment them further in your newsletter service. It would not necessarily make sense to ask them to choose your segment name, you want them to choose the product. By adding in a lookup table, you can match the chosen product with your segment name, making sure they’re added to the correct place in your list.

Delay for a specific number of days based on the day the Zap runs

Let’s say you want to send a message two business days after the Zap triggers. While you can add a Delay step to delay for two days, this will only work well if the Zap runs on Monday, Tuesday, or Wednesday. The rest of the week, adding two days will have the Zap completing on the weekend. With a lookup table you can match the day of the week the Zap starts with the number of days you need to delay for to make sure you get that two-business-day delay.

Outputting different information based on the time of day the Zap runs

To really personalize the email that is sent to your new leads, you want to include a greeting such as good morning, good afternoon, or good evening. With the lookup table, you can set the appropriate greeting to appear depending on the time of day the Zap runs.

Creating an IF/THEN statement 

With this option, you can have the lookup table send out different information depending on a result in a previous step. For example, let’s say you create a Zap that sends you a list of the meetings on your calendar for the day. But, if you don’t have any meetings, you want to send a message that your day is clear. With the lookup table, we can run the all clear message when there are no meetings, and then if there’s at least one on the list, send the list by including it in the fallback value field.

Have you used the lookup table in your workflows? Do you have a creative use for it that you’d like to share with us? We’d love to hear it!

6 replies

Hello, loved what you did with the line-item array. I tried to replicate your test but unfortunately it’s treating my comma separated input as one value.

When I try the above values I get the Fallback Value.

When I set the key to be the full comma separated value it does pass through.

Can you provide any insight as to why it’s not reading the separate values as it did in your example?


Thank you!


I have the same issue …. I’m passing a value into the key that’s clearly in the table, but it doesn’t find it for some reason. I even tried using “quotes” around the values, but it still doesn’t work - just returns the fallback value every time.

Would love some advice on how to fix that. :)

Userlevel 4
Badge +1

@bbeauchamp Is that list a comma-separated list or a  line-item array? Can you please try running it through a Formatter step before you get to your lookup table using Utilities > Text to Line-Items. Then use the output of that step in your Lookup Table step. If it’s a comma-separated list, that will turn it into the array that it’s looking for and should get this workflow working.


@Azhen It’s very likely there’s a rogue space happening somewhere in there that’s causing the trouble. If you check out your Zap History for one of the Zap runs that defaulted to the fallback and compare what’s in the table and what’s coming through from your previous step, that will help you see if there’s anything that’s keeping the two from being an exact match. Those spaces can be tricky, so check both before and after the text that you’re seeing (you can do this by highlighting the text and see if that space shows up).

Great post! Here's an additional piece posted on the Zapier Community regarding creating dynamic lookup tables with Airtable. Adding and updating content for lookup tables can be tiring and requires you to manually adjust the lookup table. With this workaround/approach, all this can be automated. Hopefully, this piece will help further automate workflows!

I understand that you can create a lookup table to convert one value into another. In my case, however, I have 500+ values I want to convert (IP addresses to hostname). I have a csv of all the conversions in two columns, but I cannot see how to create a lookup table based on that. is the only way to do it by manually creating 500+ lookups in that utility?

Userlevel 4
Badge +1

I understand that you can create a lookup table to convert one value into another. In my case, however, I have 500+ values I want to convert (IP addresses to hostname). I have a csv of all the conversions in two columns, but I cannot see how to create a lookup table based on that. is the only way to do it by manually creating 500+ lookups in that utility?

Hi @Teglon 👋

You’ll only need one lookup table as the input will be dynamic (meaning it can hold various values, depending on what information is provided from a previous step).

By adding more rows, you’ll be able to add more potential lookups, no need to create a separate lookup table for each IP to hostname.

If the lookup in the Zap is always one lookup (from IP to hostname), I would recommend importing that CSV into a Google Sheets spreadsheet, and use the Lookup Spreadsheet Row in Google Sheets action. The spreadsheet (with the 2 columns) will serve as a lookup table.

If the Zap needs to look up several IP to hostnames (through a line-item/array), then I would recommend using the Airtable workaround: 

If you still need help, feel free to contact us at: or otherwise consider hiring a Zapier Expert:

Hope that helps!