What is lookup table pricing and how does it work?

If you’re selling products with additional options that require more complex pricing, chances are you can fix it with a formula. For example, measurement-based pricing often follows the simple formula of (width x height) x unit price. But what if you can’t distill your logic into a single formula? For this scenario, we offer lookup table pricing.

What is lookup table pricing?

A lookup table is an Excel-like table holding a pair of values and their corresponding price. Here’s an example table for a custom price by “width” and “height”:

Across the horizontal x-axis on the top row you can see values in centimetres, i.e. 200, 220, etc. These are our values for the width.

Across the vertical y-axis, you’ll see the values for the height (100 cm, 120 cm, etc.).

The other values are the prices that belong to each width/height pair.

If a user enter 240cm as their width and 140cm as their height, you can use the table to see the addon price will be $76:

What if a user enters a width and height that is not seen in the table? Then the system will round up to the nearest pair, effectively allowing you to create pricing between ranges. Here’s an example:

If your user enters 210cm width by 150cm height, our plugin will find the nearest fitting pair from the table by rounding up (which is 220 by 160). The price will be set to $78.

Note that in our example, we only use 2 values (width & height), but you can also use this for just 1 value, or even more than 2 (unlimited, to be precise).

Adding a lookup table to your site

There are currently 2 different ways to add lookup-tables to your site:

1. The visual way

Use our handy add-on plugin to upload CSV (Excel/Google sheets) files.

Advantages:
The benefit is that you (or your customer) can easily manage lookup tables using your favorite editor (Excel, Google Sheets, Airtable,…), and then simply upload them to your website via our add-on. Easy-peasy!

Limitation:
CSV-based tables are only 2-dimensional, meaning only two fields can be used to influence the cost. If you need combinations of three or more fields, this method won’t work.

If you want to to use the Lookup Table add-on, read here how to use it, it explains everything further in detail.

2. The advanced (code) way

You can define lookup tables directly with PHP code as well. This requires more effort but is a free approach and it supports unlimited field combinations (as opposed to only 2, as explained above).

Below is an example showing how to create a lookup table that handles three option combinations (a 3D lookup):

In this example:

• First dimension: red or blue (could be a color, material type, etc.)
• Second: 200, 220 (e.g. width)
• Third: 100, 120 (e.g. height)
• Value: the cost

Here’s what you should know about the code snippet:

• Each of your lookup tables should have a unique name. Notice $tables['yourtablename'] where yourtablename should be replaced with the name of your table. It should only contain letters and underscores, no strange symbols or spaces are allowed.
• The table is a 3 dimensional array.
• ChatGPT is an excellent help to build a similar code snippet if you write in full what you need and what the different combinations are.

Using the lookup table in a formula

Now that you have loaded one or more lookup tables into your site, you can use formula-based pricing to use this lookup table in your pricing logic. Read our guide on formula-based pricing to familiarize yourself with how it works.

Go back to your “height” field’s Adjust Pricing setting and enter the formula: lookuptable(yourtablename;5e8ecdf;667e5e4) * [qty]

• lookuptable() is the function we’re using.
• lookuptablename is the name of your table.
• 5e8ecdf is the ID of your field for the x-axis. In our example, it’s the Width field’s ID.
• 667e5e4 is the ID of your field for the y-axis. In our example, it’s the Height fields’s ID.
• * [qty] multiplies the result with the chosen quantity on the product page.

You can find field ID’s here:

Troubleshooting

We’re written a detailed guide on troubleshooting lookup table issues here.