August 11, 2023 update: We now recommend that you use the data editor to import and edit spreadsheet data. It is significantly easier to use than the app described here.
Original text: People have been asking us for a way to look up values since we launched our first beta. In 2018, we introduced support for the CHOOSE function and wrote an app that makes it easy to convert tabular data to CHOOSE formulas, alleviating some of the pain (see below).
We’re happy to announce that we now have full support for the XLOOKUP function, which Microsoft introduced with Excel 2021 (and has been available to Microsoft 365 subscribers for a while now). If you prefer using INDEX and MATCH, we support them too (along with the new XMATCH, which has all the features of XLOOKUP).
This formula returns 300, because the sought value, 30, is the third element in the first array, which causes XLOOKUP to return the third element in the second array, 300:
The first array is known as the lookup array and the second array is known as the result array. We have written an app that does the conversion from table data to formulas, see below.
Don’t miss our extensive documentation covering XLOOKUP, INDEX, MATCH and XMATCH, with lots of examples.
XLOOKUP versus INDEX and XMATCH
Looking up a value involves two steps. First, an element matching the sought value in the lookup array must be found. Then, the corresponding element in the result array is returned. If the second element in the lookup array matches the sought value, the second element in the result array is returned.
These two steps can be performed explicitly, using XMATCH or MATCH (XMATCH is easier to use and offers more features) and INDEX. MATCH and XMATCH return the position of the sought value in the array. INDEX is then given this position as its second parameter, and returns the element at that position from the array given as its first parameter.
As a result, these formulas are equivalent:
Using XLOOKUP directly leads to shorter formulas that may be easier to understand. Combining XMATCH with INDEX has the advantage of giving you more flexibility. Which variant you choose is mostly a matter of personal preference.
What about VLOOKUP and HLOOKUP?
There is nothing that VLOOKUP and HLOOKUP can do that XLOOKUP doesn’t handle. In fact, XLOOKUP is preferable even if you use Excel, as it has more features and more sensible defaults. (You get exact matches by default.) If you have been hoping that Calcapp would some day support something like VLOOKUP and HLOOKUP, that day has finally come.
Calcapp probably won’t support VLOOKUP and HLOOKUP in the future, primarily because these functions are tied to a spreadsheet grid. Calcapp isn’t a spreadsheet, and there is no grid. When you use VLOOKUP, you specify a range encompassing your table (or parts of your table) and a column number (counting from the left) of the column containing the values you seek. Without a grid, that doesn’t make sense.
XLOOKUP is different, in that it accepts two arrays. (In Excel, it also works with ranges, which are tied to the spreadsheet grid.) Now that Calcapp has a new formula engine which supports arrays, we can easily add support for something like XLOOKUP.
We are thrilled that Microsoft happened to introduce a new lookup function at roughly the same time that we needed to add one to Calcapp — and at that, a lookup function that is fully compatible with our new formula engine. We would much rather support a lookup function pioneered by Microsoft than come up with our own variant.
Converting table data to formulas
While our XLOOKUP support is complete, there is an important caveat. All data needs to be put into formulas, because Calcapp still does not have native support for tables.
We recognize that being able to import data directly to a table in Calcapp Creator would be preferable. That feature is coming in our next-generation Calcapp 4 project.
The good news is that we have created an app that does the conversion for you. To demonstrate how to use it, we’ll use this small sample table:
| Product | Price | Color |
|---|---|---|
| Sample 1 | 5.20 | Indigo |
| Sample 2 | 5.20 | Ivory |
| Sample 3 | 5.92 | Khaki |
| Sample 4 | 5.92 | Lavender |
| Sample 5 | 7.04 | Silver |
| Sample 6 | 7.04 | Snow |
| Sample 7 | 7.55 | Tan |
| Sample 8 | 7.55 | Teal |
| Sample 9 | 9.59 | Thistle |
| Sample 10 | 9.59 | Violet |
For this exercise, we’ll need to copy column data from the table, which only works from a spreadsheet. Here is the table, as a Google Sheets spreadsheet.
To look up the price based on the product name (contained in the Product text drop-down field), we need to generate an XLOOKUP formula. To do so, we use the app.
Here’s an embedded version of the app, which you can also run as a stand-alone app:
To generate an XLOOKUP formula locating prices, follow these steps:
-
Write “Product” in the Sought value field, which causes the first parameter to the generated XLOOKUP formula to be set to “Product”.
-
Select all product names from the spreadsheet. Select Sample 1 through Sample 10, press Ctrl+C (or ⌘+C if you’re on a Mac) to copy the selection to the clipboard.
-
Paste this data into the Lookup values field in the app. Click the empty field, and then press Ctrl+V (or ⌘+V if you’re on a Mac).
-
Copy all prices to the clipboard.
-
Paste this data into the Result values field in the app.
The app generates the correct formula for you:
If you repeat the same steps for the Color column, this formula is generated:
Note that the app automatically detects if the values are numbers or text. If they are numbers (potentially starting with a currency symbol, such as “$”), the elements of the generated array are not quoted. For this to work, you need to ensure that you have selected the correct decimal separator from the Decimal separator text drop-down field.
To learn how the app was built, we have written a blog post that walks you through it.
XLOOKUP versus CHOOSE
Associating values from a table with the options available from a text drop-down field can also be done using the CHOOSE function and the Index property of text drop-down fields. We provide instructions and an app that generates such formulas.
CHOOSE formulas have the advantage that they are compact. They rely on the position of the option selected by the user and therefore don’t need to be provided with lookup values. They can also be expected to be faster.
However, CHOOSE formulas are very limited compared to XLOOKUP formulas. With XLOOKUP, you can look up any value you desire, and take advantage of all the features that XLOOKUP offers, such as inexact text matching and the like (learn more). CHOOSE formulas are mostly practical to use when you need to associate a single value with an option selected from a text drop-down field.