> For the complete documentation index, see [llms.txt](https://docs.fabricplan.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.fabricplan.com/planning-sheets/reference/formula-syntax/lookup-functions-xlookup.md).

# Lookup functions - XLOOKUP

XLOOKUP is used for condition-based measure lookups. XLOOKUP finds rows that match one or more dimension conditions and returns the requested measure value.

### How XLOOKUP works

XLOOKUP is a filtered lookup. You define the rows to keep, identify the measure to return, specify a fallback, and choose whether to return one match or every match.

* **Conditions** identify matching rows.
* **The return expression** identifies the measure or value to retrieve.
* **The fallback** is used when no row matches.
* **The output mode** controls whether one match or all matches are returned.

#### XLOOKUP is similar to `WHERE` clause in SQL

XLOOKUP works much like a SQL `WHERE` clause, but with dynamic referencing built in. It lets you describe exactly which rows should qualify while allowing selected conditions to follow the current grid context automatically.

```
Return value WHERE
    (Dimension1 == THIS) *
    (Dimension2 == "Hardcoded Value") *
    (Dimension3 == MeasureReference)
```

In this pattern, Dimension1 uses the current evaluation-context value through `THIS`, Dimension2 uses a fixed business value, and Dimension3 is compared with a value supplied by a measure reference.

After evaluating the conditions, XLOOKUP can return either all matching values as an array or the first matching value. When an array is returned, functions such as `SUM` can combine the results into one value.

**Business usecase:** This provides a flexible way to perform lookups inside a grid. A single formula can respond dynamically to the row, column, or hierarchy context while still applying fixed and measure-driven filters.

### Syntax

```
XLOOKUP(
    (condition1) * (condition2) * (condition3),
    Measure,
    0,
    1
)
```

#### Arguments

| Argument         | Example                                  | Description                                                                                        |
| ---------------- | ---------------------------------------- | -------------------------------------------------------------------------------------------------- |
| **Conditions**   | `(Region == "USA") * (Category == THIS)` | Filters the source rows. Multiplication (`*`) acts as a logical AND: every condition must be true. |
| **Return value** | `Revenue`                                | The measure or value returned from each matching row.                                              |
| **Fallback**     | `0`                                      | The value returned when no matching row is found.                                                  |
| **Output mode**  | `1` or `0`                               | `1` returns the first match. `0` returns all matching values as an array.                          |

### Understanding THIS

**THIS** means: use the current evaluation-context value of this dimension automatically.

For example, if the calculation is evaluated on the *Category* row named *Accessories*, `Category == THIS` behaves as `Category == "Accessories"`. When evaluated on another Category row, the comparison changes automatically.

`THIS` makes the calculation hierarchy-aware and reusable. You do not need to hard-code each displayed dimension member.

### Examples

```
XLOOKUP(
    (Region == "USA") *
    (Category == THIS) *
    (SubProduct == THIS),
    Revenue,
    0,
    1
)
```

**Interpretation:** Find the row where *Region* is USA and where *Category* and *SubProduct* match the values in the current evaluation context. Then, return the *Revenue* value. If there is no match, return 0. If several rows match, return the first matching value.

### Returning and aggregating multiple matches

Set the fourth argument to `0` when the conditions can match more than one row. XLOOKUP then returns an array of values. Wrap the lookup in an aggregation function to produce a single result.

| Required result   | Pattern                                |
| ----------------- | -------------------------------------- |
| **Total**         | `SUM(XLOOKUP(..., Measure, 0, 0))`     |
| **Largest value** | `MAX(XLOOKUP(..., Measure, 0, 0))`     |
| **Average**       | `AVERAGE(XLOOKUP(..., Measure, 0, 0))` |

### Referencing a subtotal

To calculate a subtotal, stop the conditions at the desired hierarchy level. Do not add conditions for lower-level children. Set the fourth argument to `0` so all matching child values are returned, and wrap the lookup in `SUM`.

```
SUM(
    XLOOKUP(
        (Region == "USA") *
        (Category == THIS),
        Revenue,
        0,
        0
    )
)
```

This returns all *Revenue* values for the current *Category* in the USA and then adds them together. *SubProduct* is intentionally omitted so all children below *Category* can contribute to the subtotal.

### Dynamic hierarchy calculation

Use `THIS` on the hierarchy dimension so the formula automatically follows the current row or grouping context.

```
SUM(XLOOKUP(
    (ProductCategory == THIS) *
    (FinancialLineItem == "Net Revenue"),
    RevenueAmount,
    0,
    0
))
```

**Interpretation:** For the current *Product Category*, find every row classified as *Net Revenue*, return the *Revenue Amount* measure, and sum the matching values.

### Common patterns

* **One exact match:** Use complete conditions and output mode `1`.
* **Subtotal or roll-up:** Stop at the parent level, use output mode `0`, and wrap with `SUM`.
* **Dynamic report row:** Use `THIS` for dimensions that should follow the current context.
* **Multiple matches, one result:** Use output mode `0` and aggregate with `SUM`, `MAX`, or `AVERAGE`.

### Important checks

* Use output mode `1` only when the first match is the intended business result. If uniqueness matters, ensure the conditions identify a unique row.
* Use output mode `0` when multiple rows are valid matches; aggregate the returned array when a scalar result is required.
* Remove lower-level conditions when calculating a parent subtotal; otherwise the lookup remains filtered to a specific child.
* Use `THIS` only where the displayed evaluation context should control the dimension value.
* Keep the fallback value appropriate for the measure. A fallback of `0` is useful for numeric measures but may not be suitable for text values.

{% hint style="info" icon="lightbulb" %}
**TIP**\
**Use the rule:** Complete conditions + mode `1` = a single lookup. Parent-level conditions + mode `0` + `SUM` = a subtotal.
{% endhint %}

### Business use cases

| Business use case                 | Definition                                                                               | Example pseudo-code                                                                              | Output                                                          |
| --------------------------------- | ---------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------ | --------------------------------------------------------------- |
| **Sales analysis**                | Retrieves revenue for the current product in a specific market.                          | `XLOOKUP((Region == "USA") * (Product == THIS), Revenue, 0, 1)`                                  | Revenue for the current product in the USA.                     |
| **Financial reporting**           | Retrieves a specific financial measure for the current product category.                 | `XLOOKUP((ProductCategory == THIS) * (FinancialLineItem == "Net Revenue"), RevenueAmount, 0, 1)` | Net revenue for the current product category.                   |
| **Budget and forecast reporting** | Retrieves planning values for the current department and account in a selected scenario. | `XLOOKUP((Department == THIS) * (Account == THIS) * (Scenario == "Forecast"), Budget, 0, 1)`     | Forecast budget for the current department and account.         |
| **Regional reporting**            | Aggregates revenue across all matching records for the current region.                   | `SUM(XLOOKUP((Region == THIS), Revenue, 0, 0))`                                                  | Total revenue for the current region.                           |
| **Customer analysis**             | Retrieves revenue for a specific customer and product combination.                       | `XLOOKUP((Customer == THIS) * (Product == THIS), Revenue, 0, 1)`                                 | Revenue for the current customer and product.                   |
| **Cost analysis**                 | Aggregates expenses for the current department and expense category.                     | `SUM(XLOOKUP((Department == THIS) * (ExpenseType == THIS), Expense, 0, 0))`                      | Total expenses for the current department and expense category. |
| **Hierarchy-based reporting**     | Calculates a subtotal by retrieving all child values under the current parent member.    | `SUM(XLOOKUP((Region == "USA") * (ProductCategory == THIS), Revenue, 0, 0))`                     | Total revenue for the current product category in the USA.      |
| **Management reporting**          | Creates a reusable calculation that follows the current row or hierarchy context.        | `XLOOKUP((ProductCategory == THIS) * (FinancialLineItem == "Net Revenue"), RevenueAmount, 0, 1)` | Net revenue for each product category displayed in the report.  |
| **Multi-record analysis**         | Retrieves multiple matching values and aggregates them into a single result.             | `SUM(XLOOKUP((Region == "USA") * (ProductCategory == THIS), Revenue, 0, 0))`                     | Combined revenue from all matching records.                     |


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.fabricplan.com/planning-sheets/reference/formula-syntax/lookup-functions-xlookup.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.