The Value Lookup element is designed to simplify calculations by finding a value from a spreadsheet table based on the user input values. It eliminates the need of complex conditional formulas by allowing you to import a CSV file with your data.
- The Element Label input field lets you name your element and the toggle beside it controls whether the title is visible or hidden
- The Element Tooltip is a text field that lets you give more context to each element or explain what information you require to input. Once enabled, it will appear as a small info icon next to the element title.
- The Import CSV button allows you to browse files on your device.
- Check columns and rows selectors control which elements’ input values are used to look up imported table data.
- The Display Value toggle controls whether the element is visible or hidden.
- The Show value only in email notifications toggle allows hiding the element from a calculator but includes its value in email notifications (using the Mail Results or Order Form tools).
- The Reveal value after submission toggle makes the value will appear only after a successful submission is made via Order Email or Mail Results elements
- The Decimal Places slider lets you choose how many numbers are visible after the decimal point for a more accurate result.
- The Prefix input field lets you choose what you want to be written before the result.
- The Postfix input field lets you choose what you want to be written after the result.
How to prepare a CSV file?
CSV file has to be prepared in a specific in order to correctly import into the Value Lookup element.
- The first row’s and A column’s data in the file will be used to find the value from a table.
- The first row’s data corresponds to the element’s value selected in the Check columns selector.
- The A column’s data corresponds to the element’s value selected in the Check rows selector.
- The first row’s and A column’s data can be expressed as a range or specific value. Then expressed a range, the cell has to maintain the x to y format; the word to must be used between two values. E.g. 1 to 10
Two ranges should not overlap or share the same range’s start/end value. Example of incorrect ranges:
1 to 5 and 3 to 10
1 to 5 and 5 to 10
Correctly prepared first row and A column:
- The decimal separator must be a period (or full-stop) symbol for any value in the spreadsheet.
- The value table must contain numeric values only - any text usage might result in an error.
Correctly prepared spreadsheet:
Once the spreadsheet is prepared correctly, export/download it in CSV format and import it to the Value Lookup element.
How this works?
Value Lookup element looks up a value in a CSV file based on two values. One from the first row and one from the first column.
For the value lookup element to output a result, it needs two values that will be taken from your calculator elements. These values can be static or dynamic based on the customer’s choices.
Here is an example of a correctly prepared sheet for a wallpaper price calculator.
The calculator has two input elements, one is used for the customer to input the product width and another to input product height.
Value Lookup element in this case is used to find the price for the specific dimensions. One element is added to check the columns, and the other to check the rows.
A correctly setup Value Lookup element settings:
Once the customer inputs Width value to be 60 cm and Height value to be 170 cm, the Value Lookup element finds and returns a value of 305 from the file.
Make sure that your CSV file only contains numeric values for the Value Lookup to function correctly. If you have any text values or options, assign them a corresponding numeric value in the element settings and adjust your file accordingly.
Ensure that numeric values in the spreadsheet are used without any additional formatting or symbols in the cells. Utilize the Plain Text setting in software like Excel or Google Sheets for accurate results.