> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Lookup

> Lookup

<Panel>
  <Info>
    Cluster requirements:

    * UC dedicated clusters 14.3+ supported
    * UC standard clusters 15.4+ supported
    * Livy clusters 3.0.1+ supported
  </Info>
</Panel>

Lookup gems allow you to mark a particular DataFrame as a *Broadcast* DataFrame. Spark ensures that this data is available on every computation node so you can perform lookups without shuffling data. This is useful for looking up values in tables.

<Info>
  Lookups are implemented as user-defined functions under the hood in Prophecy. Learn about UDF
  support in Databricks on our documentation on cluster [access
  modes](/data-engineering/fabrics/spark-provider/databricks/UCShared).
</Info>

## Parameters

| Parameter     | Description                                                               |
| ------------- | ------------------------------------------------------------------------- |
| Range Lookup  | Whether to perform a lookup with a minimum and maximum value in a column. |
| Key Columns   | One or more columns to use as the lookup key in the source `DataFrame`.   |
| Value Columns | Columns to reference wherever you use this Lookup gem.                    |

## Use a Lookup gem

After creating a Lookup gem, you can use the lookup in other gem expressions.

### Column-based lookups

Assume you created this Lookup gem with the following configurations:

<img src="https://mintcdn.com/prophecy-62973bd0/YMU5yAViYYX3rOGW/data-engineering/gems/source-target/img/lookup_ui.png?fit=max&auto=format&n=YMU5yAViYYX3rOGW&q=85&s=94c418ef0abdd31e0709df6b43f6c343" alt="Lookup UI" width="1283" height="770" data-path="data-engineering/gems/source-target/img/lookup_ui.png" />

To perform a column-based lookup, use:

<CodeGroup>
  ```python example.py theme={null}
  lookup("MyLookup", col("customer_id")).getField("order_category")
  ```

  ```scala example.scala theme={null}
  lookup("MyLookup", col("customer_id")).getField("order_category")
  ```

  ```sql example.sql theme={null}
  MyLookup(customer_id)['order_category']
  ```
</CodeGroup>

Assume you also have the following [Reformat](/data-engineering/gems/transform/reformat) component:

<img src="https://mintcdn.com/prophecy-62973bd0/YMU5yAViYYX3rOGW/data-engineering/gems/source-target/img/lookup_use.png?fit=max&auto=format&n=YMU5yAViYYX3rOGW&q=85&s=16a55566063240cc54eddaa1751d8f29" alt="Reformat example" width="1260" height="182" data-path="data-engineering/gems/source-target/img/lookup_use.png" />

Here, you have a column named `category` that is set to the value of `MyLookup(c_id)['order_category']` in SQL Expression mode. Whatever the value of `order_category` is for the key found in the `c_id` column, which is compared to the source `customer_id` key column, the Lookup gem uses it for a new column.

### Literal lookups

You can use any column reference in a Lookup expression. This means that you can use Lookups with static keys:

<CodeGroup>
  ```python example.py theme={null}
  lookup("MyLookup", lit("0000")).getField("order_category")
  ```

  ```scala example.scala theme={null}
  lookup("MyLookup", lit("0000")).getField("order_category")
  ```

  ```sql example.sql theme={null}
  MyLookup('0000')['order_category']
  ```
</CodeGroup>

This expression evaluates to the value of `order_category` where `customer_id` is `0000`. This is useful in situations where you want to have a table of predefined keys and their values available in expressions.