Tip: Use XLOOKUP with multiple criteria

November 14, 2025

XLOOKUP is excellent for finding data in tables. Let’s explore how to use it with multiple search criteria using this venue table:

Venue	Type	Location	Capacity	Rate	AV	Catering
Grand Ballroom	Hotel	Downtown	500	5000	TRUE	TRUE
Conference Center A	Conference	Business District	200	2000	TRUE	FALSE
Community Hall	Community	Suburbs	150	800	FALSE	TRUE
Rooftop Terrace	Outdoor	Downtown	100	1500	FALSE	FALSE
Executive Boardroom	Conference	Business District	25	500	TRUE	FALSE
Garden Pavilion	Outdoor	Suburbs	80	1200	TRUE	TRUE

The table is created using the data editor and stored in a form screen named Venues.

Basic XLOOKUP usage

Normally, you use XLOOKUP to find exact matches:

This finds the first venue where the type equals “Hotel” and returns “Grand Ballroom”.

Moving to the TRUE technique

You can achieve the same result by moving the equality check into the search criteria:

Here’s what happens: XLOOKUP evaluates Venues!Type = "Hotel" for each row. This comparison returns TRUE for “Grand Ballroom” (because its type is “Hotel”) and FALSE for all other venues. Since we’re looking for TRUE, XLOOKUP finds the first row where the comparison is TRUE and returns “Grand Ballroom”.

Both formulas return the same result, but the second approach opens up powerful possibilities. Instead of a simple equality check, you can use any logical expression that evaluates to TRUE or FALSE.

Building complex search criteria

Once you’re searching for TRUE, you can combine multiple conditions using logical operators. Calcapp supports these operators that are common in programming languages:

• && means “and” — both conditions must be TRUE.
• || means “or” — either condition can be TRUE.
• ! means “not” — turns TRUE into FALSE and vice versa.

(You can also use the function equivalents AND, OR and NOT, but the operators are often easier to read.)

For example, to find conference venues with capacity of at least 100:

This returns “Conference Center A” — the first venue meeting both criteria.

Demo app

Here’s a working example that lets you experiment with different search combinations:

Try this:

• Set venue type to “Hotel” — you’ll find “Grand Ballroom” at $5000.
• Now add a maximum budget of $4000 — the search will find “Hotel Atrium” instead.
• Change venue type to “Outdoor” — you’ll find “Rooftop Terrace”.
• Toggle “Requires catering” on — now it finds “Garden Pavilion” instead.

How the demo app works

The demo app shows several search criteria working together:

• Venue type: Drop-down to filter by hotel, conference, community or outdoor venues.
• Minimum capacity: Number field for required seating.
• Maximum budget: Number field for budget constraints.
• Requires AV equipment: Switch field for audio/visual needs.
• Requires catering: Switch field for food service needs.

When you change any criteria, the app immediately shows the first venue that matches all your requirements.

The XLOOKUP formula

Here’s the core formula that powers the search:

This single formula handles all the search logic. The following sections explain its constituent parts:

Venue type

Matches if no type selected or if venue type matches selection.

Capacity

Matches if no minimum specified or if venue has enough capacity.

Budget

Matches if no budget specified or if venue is within budget.

AV equipment

Matches if audio-visual equipment not required or if venue has AV.

Catering

Matches if catering not required or if venue has catering.

All conditions are combined with &&, so venues must meet every specified criterion.

Techniques demonstrated

Optional criteria handling

In order to make criteria optional, the formula uses this pattern:

If a search field is blank, that criterion is ignored. If it has a value, the venue must match it.

Switch field logic

For switch fields, the formula uses (!RequiredAV || Venues!AV)(!RequiredAV || Venues!AV). When the switch is FALSE (not required), the condition is always TRUE. When the switch is TRUE (required), venues must have that amenity.

Multiple result fields

The app shows venue name, location, capacity and rate by using the same search criteria in separate XLOOKUP formulas:

This ensures all displayed information comes from the same matching venue.

The TRUE search technique

The takeaway is that you can use TRUE as the lookup value instead of searching for specific data. When you write:

XLOOKUP evaluates the complex condition for every row, looking for the first position where it equals TRUE. This lets you use any logical expression as your search criteria.

This technique works with any combination of:

• Comparison operators: =, <>, <, >, <=, >=.
• Logical operators: &&, ||, !.
• Parentheses for grouping complex conditions.
• Field references and literal values.
• Functions like ISBLANK for optional criteria.

Building your own search apps

This approach works for any data table where you need sophisticated filtering:

• Product catalogs: Search by category, price range, features and availability.
• Employee directories: Filter by department, role, location and skills.
• Event listings: Find events by date, type, location and ticket price.
• Inventory systems: Search by product type, quantity, supplier and status.

The pattern is always the same: combine multiple conditions with logical operators, then let XLOOKUP find the first matching row. Use the same criteria in multiple formulas to retrieve different fields from that row.

Try the demo app above to experiment with different search combinations and see how multiple criteria can help you find exactly the data you need.