Looking up values

Many of the calculations you perform are likely linked to data, which is often stored as spreadsheet tables. That data may be prices, product information or something else. Regardless, you need a way to look up values that correspond to other values.

While data lookups could be performed with the flexible IF function, doing so is rarely practical. Instead, we suggest that you use the CHOOSE function to associate values with drop-down field choices and the robust XLOOKUP function for everything else.

Calcapp requires data to be stored inside the formulas themselves. We provide two apps for doing the conversion, one generating CHOOSE formulas and one generating XLOOKUP formulas. This guide walks you through the process, first using CHOOSE and then using XLOOKUP.

Drop-down fields and CHOOSE

The CHOOSE function selects what parameter to return based on the number given as the first parameter. If 2 is given, the third parameter is returned, if 3 is given, the fourth parameter is returned, etc. This formula returns “two”:

Drop-down fields have a property named Index, which returns 1 if the first option is selected, 2 if the second option is selected, etc. This is perfect for CHOOSE — we just need to plug in the index of the selected option as the first parameter and provide the values associated with the options as the rest of the parameters:

The process of doing the conversion with our app is described below. You can also watch a video of the process.

Let’s say that you want to convert a small table to an app:

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.

We’ll represent the three columns Product, Price and Color as three fields in the app, with the same names. The Product field should be a text drop-down field, the Price field should be a currency field and the Color field should be a text field:

Making a selection in the Product field should update the information in the Price and Color fields. Those two fields should be associated with formulas using the CHOOSE function in conjunction with the Index property of the Product field.

To transfer the product names from a spreadsheet, select all of them and copy them to the clipboard. Then, select the Product drop-down field and click its Edit as text button to edit the values as text instead of a list. You can then position the cursor in the text box and paste the values from the clipboard:

Here is the app you need to use for the conversion:

Run a standalone version of the app.

To convert the Price columns to two formulas you can associate with the corresponding fields, follow these steps:

1. Write “Product” in the Drop-down field name field, which causes the first parameter to the generated CHOOSE formula to be set to Product.IndexProduct,Index.

2. Select all prices from the spreadsheet and press Ctrl+C (or ⌘+C if you’re on a Mac) to copy the selection to the clipboard.

3. Paste this data into the Column data field in the app. Click the empty field, and then press Ctrl+V (or ⌘+V if you’re on a Mac).

You’ll find that CHOOSE formulas are created, one suitable for number fields and one suitable for text fields. Use the generated formula for number fields:

Associate the formula above with the value property of the Price field.

Repeat the process for the Color column. This time, you need to copy the generated formula for colors:

Associate the formula above with the value property of the Color field.

Refer to our video tutorial for a demonstration.

We recommend that you continue maintaining your tabular data in spreadsheets and convert your columns to formulas every time you make an update.

The XLOOKUP function

While CHOOSE formulas work well with drop-down fields, looking up values from a table is something you likely need to do in other contexts as well. For those uses, the XLOOKUP function works great.

You may be used to the functions VLOOKUP and HLOOKUP from spreadsheets. XLOOKUP is a new function Microsoft introduced with Excel 2021.

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.)

We’ll use the same table as above for demonstrating XLOOKUP. Again, a spreadsheet version of the table is available as a Google Sheets spreadsheet.

Here is the app you need to use for the conversion:

Run a standalone version of the app.

To generate an XLOOKUP formula locating prices, follow these steps:

1. Write “Product” in the Sought value field, which causes the first parameter to the generated XLOOKUP formula to be set to “Product”.

2. 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.

3. 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).

4. Copy all prices to the clipboard.

5. 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.

XLOOKUP versus CHOOSE

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.

Continue reading about customizing the appearance of your apps »