Use the advanced lookup to fetch values from another sheet or query and use them in planning formulas. Configure the dimensions that identify a value, choose the measure to retrieve, and reference the lookup in a formula.
For example, you can bring a forecast from another sheet into your planning view by matching Region and Product. You can then use the retrieved forecast in further calculations without manually copying values.
The following table describes typical scenarios for using an advanced lookup in your planning sheets.
| Planning need | Example lookup |
|---|---|
| Compare plans with a shared forecast | Retrieve Forecast by Region and Product from another sheet. |
| Use common planning assumptions | Retrieve a growth rate or unit cost by Product and Year. |
| Apply currency assumptions | Retrieve exchange rates by Currency and Period. |
| Reference prepared query results | Retrieve target or budget measures from a query using matching categories. |
Choose categories that identify the value at the level you need. For a monthly exchange rate, currency alone may not be enough; include the relevant period as well.
Before you begin, open a planning sheet in editing mode and assign the fields needed for your planning view. Make sure the source sheet or query and its fields are available in the Data pane.
You can add an advanced lookup directly from the field settings pane. The following steps guide you through creating the lookup before referencing it directly in your formulas.
A new lookup appears with an automatically assigned name, such as Lookup 1. Note this name; you will use it in formulas.
| Bucket | What to add | Example |
|---|---|---|
| Category | Dimensions that identify the required value | Region, Product, Year, Quarter, or Month |
| Values | The measure to retrieve | Forecast, Exchange Rate, Unit Cost, or Target |
The lookup sheet used in this example is shown in the following image.
To fetch the Rebate Rate from the Rebate Plan sheet, configure the lookup as shown in the following image. The granularity in the lookup sheet is at product category > subcategory level; however, you can still fetch values at a region > category level using a lookup.
After configuring an advanced lookup, use the LOOKUP function in your formulas to retrieve measure values from other planning sheets.
Lookup syntax:
LOOKUP("advancedLookupName", "measure", [rows], [columns])| Argument | Meaning |
|---|---|
| advancedLookupName: string | The auto-assigned lookup name. Example: "Lookup 1". |
| measure: string | The original source field name for the measure assigned to Values. This is not the title of the new formula. |
| rows: [string] (Optional) |
|
| columns: [string] (Optional) |
|
In the target planning sheet, go to Insert Column > Formula in the Planning ribbon. Create a formula using the LOOKUP function.
The lookup retrieves data at the leaf level of its assigned dimensions. The region-level rows display "#N/A" because the region dimension exists only in the target sheet (and not in the lookup). Set the row aggregation type to Sum. This allows the target sheet to roll up the fetched leaf-level values to the region level.
You can use the ROW and COLUMN identifiers or directly reference values from another planning sheet when the row and column dimensions in the source planning sheet match the categories in the lookup.
The following image shows a planning sheet where Country and Category are set as row dimensions, and Quarter is set as the column dimension. These same dimensions are configured in the lookup for the Price Forecast visual.
To reference the Unit Price Forecast in a formula within the Product Plan sheet, use the following syntax:
The shorter form uses the current row and column context automatically:
LOOKUP("Lookup 1", "Forecast")Use this syntax only when the planning sheet's dimension values and their order match the lookup categories.
To retrieve North forecast for Bikes regardless of the current cell:
LOOKUP("Lookup 1", "Forecast", ["North"], ["Bikes"])For multiple dimensions, list their values in the required order. For example, a lookup with categories Currency followed by Period can use:
LOOKUP("Lookup 2", "Exchange Rate", ["EUR", "2026-01"], [])Here, both matching values are supplied in the first list. The empty second list,[], prevents additional values from the current column context from being included.
The field names and category labels in these examples are illustrative. Replace them with your configured lookup's names and actual source values.
Use the preview to check that the source contains the category combinations your formula will use and that the selected value field returns the expected figures.
The preview is a sample limited to 100 rows, not a complete source-data listing. A value missing from the sample is not necessarily missing from the source. The preview icon is disabled while the lookup has no assigned fields.
When a planning sheet loads, it automatically retrieves lookup data for any saved formula that uses it.
Creating a lookup on its own won't trigger data loading—it only loads when referenced in an active formula. To check or verify a newly created lookup before adding it to a formula, use the Preview feature.
In this use case, you build a Revenue Forecast by using integrated planning with a lookup, rather than isolated spreadsheets. This approach reduces error rates and updates both plans dynamically when lead times or market prices shift.
Planned Regional Sales: Create a planning sheet to capture the planned quantity. Assign the row and column dimensions as per the following table.
| Row dimensions |
|
| Column dimensions |
|
Use this target planning sheet to create a lookup that retrieves price forecast values from the Product Price Forecast sheet.
| Row dimensions |
|
| Column dimensions |
|
Use this planning sheet as the lookup source for the Price Forecast measure.
When adding dimension names to your formula, press Ctrl + Space to open the suggestions menu and select them from the References tab instead of typing them manually.
Select a Planning visual in editing mode, open Fields > Advanced.
Check the From Sheets and Queries sections in the Data pane. Confirm that the source is available and that you can access its fields.
Assign fields to the lookup. For a relevant result, configure both Category and Values.
Recheck the source and assigned fields, including any relationships needed to combine them. Retry after the source is ready. If the error persists, contact your support team.
Ensure the lookup name used in the formula matches the auto-generated label. Check the original value field name, the dimension category order, and the exact labels. Verify that the complete row and column combination exists in the source planning sheet.
Compare the row and column context in both cells. Extra hierarchy levels or a different category order can change the match. Try explicit lists to isolate the combination, for example, LOOKUP("Lookup 1", "Forecast", ["North"], ["Bikes"]).
Check the selected measure and its aggregation. Confirm that your categories identify the intended level of detail, especially for exchange rates or unit costs.
The preview option shows only a sample of up to 100 rows. Check the full source before concluding that the combination is missing.
Confirm that the source itself has updated values. Use Sync or Refresh Data, then reopen the visual if needed and compare it to a known value.
Yes. Select Add Lookup again and use the designated name for each lookup in the related formulas.
No. It retrieves a value for use in a formula; it does not write the result back to the source.
No. Use the supported LOOKUP syntax covered in earlier sections. Do not add Excel table ranges or column-number arguments.
