What is lookup table pricing and how does it work?
When selling products with customizable options, you sometimes need a pricing system that is more complicated than a simple formula.
For example, if you sell custom-sized products, you might be able to calculate the price with a formula such as:
(width × height) × price per unit
But not every pricing model works that way. Sometimes the price depends on a combination of options, and you already have a specific price for each possible combination. In that case, a lookup table is a much better solution.
What is lookup table pricing?
A lookup table is essentially a spreadsheet containing different combinations of product options and the price that belongs to each combination.
When a customer selects their options, the system looks up the matching combination in the table and uses the corresponding price.
Prices based on 2 fields
For example, imagine you sell custom blinds. The price depends on both the width and height. You could create a table like this:
The values along the top (row 1) represent the available widths, while the values on the left (column A) represent the available heights.
Every cell contains the price for that particular combination. So, if a customer chooses:
- Width: 240 cm
- Height: 140 cm
the lookup table returns a price of $76.
What if the customer’s measurements aren’t in the table?
You don’t necessarily need to create a row for every possible measurement. For example, suppose your table contains 200 cm and 220 cm for width, but the customer enters 210 cm. The lookup table can round up to the next available value.
For example, if the customer enters 210 cm × 150 cm, the system selects the nearest available size, 220 cm × 160 cm, and uses its price of $78.
What if the price depends on more than two options?
Lookup tables aren’t limited to two dimensions. If your price depends on three or more options, you can use a list where each row represents one possible combination. The last column is the price for that combination.
For example, imagine a printing company selling postcards. The price depends on:
- Quantity
- Paper size
- Paper type
The lookup table could look like this:
If the customer selects 1,000 × 8×10 × Glossy, the lookup table returns $56.
You can have as many columns as necessary, allowing you to build pricing based on complex combinations of options.
When should you use a lookup table?
A lookup table is useful when your pricing is based on predefined combinations rather than a straightforward mathematical formula.
For example:
- Custom-sized products or printing & packaging
- Products where material affects the price
- Products with quantity-based pricing
- Products where several options together determine the price
- Any situation where you already have a price list in Excel
If you can express your pricing as a mathematical formula, formula-based pricing is usually simpler.
But if you already have a large price matrix or price list, a lookup table can save you from trying to recreate that pricing logic with complicated formulas.
How to add a lookup table to your WooCommerce store
There are two ways to create lookup tables with Advanced Product Fields.
1. Use the Lookup Table add-on
The easiest option is to create your pricing table in a spreadsheet and upload it as a CSV file using our handy add-on plugin. You can manage the table using tools you’re already familiar with, such as Google Sheets, Excel, Airtable, …
Once you’ve uploaded the CSV, you can use the lookup table in your product pricing.
A table with one or two dimensions can use a grid layout, while tables with three or more dimensions use a list layout.
Read the guide to using the Lookup Table add-on
This approach is particularly useful if your prices change regularly, because you can update the spreadsheet and upload the new version without having to manually edit your pricing logic.
2. Create the lookup table with PHP
If you prefer to manage everything in code, you can also define lookup tables directly with PHP. This is a more advanced approach, but it’s free and doesn’t require the Lookup Table add-on.
Below is an example showing how to create a lookup table that handles three option combinations (a 3D lookup):
Supply your license key to unlock the code
Since this is a custom-coded snippet, please enter your license key so we can verify your purchase.
In this example:
- First dimension:
redorblue(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. - In our example, the table is a 3 dimensional array, but you can use any dimension.
- 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 a lookup table in a pricing formula
Once your lookup table has been created, you can use it in an Advanced Product Fields pricing formula with the lookuptable() function.
Example 1: width & height
Let’s go back to our custom-size product. You have two fields:
- Width
- Height
Your lookup table contains the prices for each width/height combination. Your pricing formula would look something like this:
lookuptable(yourtablename; width_field_id; height_field_id) * [qty]
lookuptable()tells APF to retrieve a value from a lookup table.yourtablenameis the name of your lookup table. If you uploaded a CSV, this is the CSV filename.width_field_idis the ID of your Width field.height_field_idis the ID of your Height field.[qty]multiplies the result by the quantity selected by the customer.
Example 2: 3 or more options
For our postcard example, the lookup table contains:
| Quantity | Paper size | Paper Type | Price |
|---|---|---|---|
| 500 | 4×6 | Glossy | 30.00 |
| 500 | 4×6 | Matte | 34.00 |
| 1000 | 4×6 | Glossy | 35.00 |
| 1000 | 4×6 | Matte | 39.00 |
| 500 | 8×10 | Glossy | 46.00 |
| 500 | 8×10 | Matte | 52.00 |
| 1000 | 8×10 | Glossy | 56.00 |
| 1000 | 8×10 | Matte | 62.00 |
On your product, you would create a field for each of these options. You can then use a formula such as:
lookuptable(postcards; quantity_field_id; size_field_id; paper_type_field_id)
The order of the field IDs in your formula should match the order of the columns in your lookup table.
APF uses the customer’s selections to find the matching row and returns the corresponding price.
Finding field ID’s
When using the lookuptable() function, you’ll need the IDs of the product option fields that correspond to the columns in your table. You can find these IDs in the Advanced Product Fields interface:
Troubleshooting
Lookup tables are extremely flexible, but pricing systems can become complicated quickly.
If you’re not sure whether a lookup table is the right solution for your product, or you’re having trouble setting one up, contact our support team and we’ll be happy to point you in the right direction.
We’re also written a detailed guide on troubleshooting common lookup table issues here.