> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://help.sigmacomputing.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://help.sigmacomputing.com/_mcp/server.

# LookupMatchNulls

> Use the Sigma LookupMatchNulls function to find matching data in another data element, including rows where both join keys are null.

The **LookupMatchNulls** function finds matching data inside a data element, either in the same table or in an external table. It works exactly like the **[Lookup](/docs/lookup)** function, except that it also matches rows where the join keys on both sides are `null`.

**Lookup** compares join keys for equality, and a `null` value is never equal to another `null` value. As a result, any row with a `null` join key returns no match. **LookupMatchNulls** treats two `null` join keys as a match, so those rows return a value instead.

To apply this function without directly using the formula bar, select **Match null values** in the **Add Lookup** modal. For more information, see [Add columns through Lookup](/docs/add-columns-through-lookup).

All elements must be on the same data connection.

## Syntax

```
LookupMatchNulls(formula, local key 1, external key 1, [local key 2], [external key 2], ...)
```

Function arguments:

|                    |                                                                                               |
| :----------------- | :-------------------------------------------------------------------------------------------- |
| **formula**        | The formula to compute (or the target column to reference) for the row in the target element. |
| **local key 1**    | The column to use as a join key in the local data element.                                    |
| **external key 1** | The column to use as a join key in the target data element.                                   |
| **local key 2**    | (optional) The additional column to use as a join key in the local data element.              |
| **external key 2** | (optional) The additional column to use as a join key in the target data element.             |

## Notes

* Because `null` join keys match each other, a local row with a `null` key matches every row in the target element that also has a `null` key. When the target element contains more than one row with a `null` value for the key, the match isn't unique and returns *Multiple values* as a result.
* When you apply an aggregate function to the *formula* argument, **LookupMatchNulls** behaves as a rollup and continues to match `null` join keys.

## Examples

### Match null values with one external key

```
LookupMatchNulls([Customers/Region], [Cust Key], [Customers/Cust Key])
```

In this example, a table containing order data has customer data added onto it from the **Customers** table using a lookup. The two tables are joined using a single column (join key) from each table. In this case, both join keys are named **\[Cust Key]**.

The *Customers* table has one row with a `null` **\[Cust Key]** value, which shows a region value of `Unassigned`. Orders `1002` and `1003` have `null` values for the *\[Cust Key]* column, so both match that row and return `Unassigned`.

![The Customers table lists three customers, one with an empty Cust Key and the region Unassigned. The Order Customer table lists four orders, and the Region (Customers) column returns Unassigned for the two orders with empty Cust Key values.](https://fdr-prod-docs-files-public.s3.us-east-1.amazonaws.com/sigma.docs.buildwithfern.com/e807d8c2aa14e7e39ef720bbb9d0b2c51d6c373ca5a301fcbabf8c721aa7bf2e/assets/docs-images/lookupmatchnulls-single-null-key.png?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Content-Sha256=UNSIGNED-PAYLOAD&X-Amz-Credential=AKIA6KXJSKKNFOCF7G4B%2F20260903%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20260903T205741Z&X-Amz-Expires=604800&X-Amz-Signature=364d9574cfa63b510cb1da81f62e21f0b22e86ae591859d3e4d805270938c9c0&X-Amz-SignedHeaders=host&x-amz-checksum-mode=ENABLED&x-id=GetObject)

In formulas, reference columns from other tables with the **\[table name/]** prefix (e.g. **\[Customers/Region]**).

### Match null values with two external keys

```
LookupMatchNulls([Price List/Price], [Product], [Price List/Product], [Promo Code], [Price List/Promo Code])
```

In this example, a table containing order data has price data added onto it from the **Price List** table using a lookup. The two tables are joined using two columns (join keys) from each table. In this case, the join keys are named **\[Product]** and **\[Promo Code]**.

The *Price List* table has two rows with a `null` **\[Promo Code]** value, which show the standard price for each product. Orders `2` and `3` have `null` values for the *\[Promo Code]* column, so each one matches the row with the same **\[Product]** value and returns that product's standard price.

![The Price List table lists three prices, two with an empty Promo Code. The Order Product table lists three orders, and the Price (Price List) column returns the standard price for the two orders with empty Promo Code values.](https://fdr-prod-docs-files-public.s3.us-east-1.amazonaws.com/sigma.docs.buildwithfern.com/eca3bab384c2fdae017fa2ced50cb1ae4112303515578103c77e6ebfb129a43e/assets/docs-images/lookupmatchnulls-multiple-null-keys.png?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Content-Sha256=UNSIGNED-PAYLOAD&X-Amz-Credential=AKIA6KXJSKKNFOCF7G4B%2F20260903%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20260903T205741Z&X-Amz-Expires=604800&X-Amz-Signature=be1988b6f30d2799628de5e57c0492248f7d7ace000fa7c6be955fb880eb9394&X-Amz-SignedHeaders=host&x-amz-checksum-mode=ENABLED&x-id=GetObject)

## Related resources

* [Lookup](/docs/lookup)
* [Rollup](/docs/rollup)
* [Add columns through Lookup](/docs/add-columns-through-lookup)