## What is Tableau Lookup Function?

An essential aspect of data analysis is to compare the current value with previous values and find the apparent difference between them. It is more so when you need to calculate previous values with the current value. This is made easy by using an inbuilt function in Tableau called the Lookup function. The Lookup function, as it says in the name, “Looks up” for previous values depending on the number of places you enter. In the example below, the Lookup function is used to see two places below the actual value.

See the example of a Tableau Lookup value in another column below.

For this, the values of “Sales” are sought out for two places above the actual value.

##### Table of contents

###### Key Takeaways

- The Tableau Lookup function enables users to retrieve values from a dataset based on specified conditions.
- It offers flexibility in data analysis by allowing users to reference values from different rows or columns.
- Users can customize the lookup conditions to meet their specific analysis requirements.
- With the Lookup function, users can perform dynamic calculations and comparisons within their visualizations.
- Lookup functions are particularly valuable for analyzing trends, patterns, and relationships within datasets.
- Users can combine Lookup functions with other Tableau features to create powerful analytical solutions.

### Syntax

The Syntax for using Tableau Lookup with condition is:

**LOOKUP(expression, [offset])**

Where,

**expression **is the formula or column used and,

**[offset] **is the number of values above the current value.

### How to use Lookup function in Tableau?

Learn how to create a Tableau dashboard design by following the steps below.

**Step 1: **In the Tableau Application, either select a new Book or skip this step if you’re newly opening the Tableau application.

It will open a new workbook, where you can add sheets and dashboards by selecting it at the bottom of the application.

**Step 2: **Drag and drop the dataset you want to perform various analyses.

The Sample-Superstore dataset is used for this example.

**Step 3: **Go to the new sheet to start your visualization.

**Step 4: **Drag and drop the Order Date in the columns section.

**Step 5: **Right-click on the “Order Date” and select the “Custom” format as shown.

Select the custom format as “Month/Year”.

**Step 6: **Place the “Sales” feature in the Columns tab, right-click and select the “Discrete” value.

Now these two are in the rows section.

**Step 7: **Create a new Calculated field.

**Step 8: **In the calculated field, name the field.

**Step 9: **Write the formula to find the difference between the “Sales” feature and the Tableau Lookup value in another table (previous value).

In this formula:

**SUM([Sales]):**This calculates the sum of the values in the [Sales] field.**ZN():**In Tableau, ZN() is used to replace NULL values with zeros. It converts a NULL value to zero, and if the value is not NULL, it returns the original value.**ZN(SUM([Sales])):**This wraps the sum of sales within the ZN() function, ensuring that if the sum of sales is NULL or empty, it will be treated as zero.**LOOKUP():**In Tableau, the LOOKUP() function is used to look up the value of an expression at a specified offset in the data partition. It is often used in table calculations.**LOOKUP(ZN(SUM([Sales])),-1):**It is used to get the previous value for the “Sales” column.

**Step 10: **Place the new calculated field in the **Text** section of the “Marks” tab.

With that, a lookup table with the difference between the Tableau Lookup previous value is done.

### Examples

See some exciting examples with which you can use the Tableau Lookup value in another table in different ways. Some of these uses Calculated fields and require you to use formulae, and in others, the Lookup value can be defined without any formulae. There are examples covering both scenarios below.

#### Example #1: Calculate difference between current and previous values

In some cases, you want to find the difference between the current value and the previous value. It is an integral part of data analysis that is tied loosely to data science. In this example, the Sample-Superstore dataset is used, and the difference in Discount is calculated using Tableau Lookup’s previous value function.

**Step 1: **Open a new Workbook. In that, connect it with the Sample-Superstore dataset.

**Step 2: **A “Sheet1” is automatically defined in the new workbook. Go to that.

**Step 3: **In the rows, select “Ship Date” and click on the ‘+’ in the YEAR to expand to QUARTER as shown.

**Step 4: **Drag and drop “Discount” in the “rows” tab. Select “Discrete” for it to show numerical values instead of a graph visualization.

**Step 5: **Create a calculated field to find the previous discount value.

**Step 6: **Use the Tableau Lookup previous value as shown. ‘-1’ is used to show the previous value.

**Step 7: **Add this calculated field to the Rows and make it “Discrete” by right-clicking on the calculated field and selecting “Discrete.”

Below is the table.

**Step 8: **Create a new calculated field to find the difference between the current value and the previous value.

**Step 9: **In the field, subtract the Discount value with the “Prev_Discount” calculated earlier.

**Step 10: **Drag and drop the calculated field in the “Text” section in the “Marks” tab.

Below is the final table.

#### Example #2: Calculate Percentage change

Similar to the above example, you can calculate the percentage change in the “Sales” of different sub-categories over the years. This can be done using the Lookup function, which can be used to get the previous value and make percentage change comparisons based on that.

**Step 1: **Create a new Worksheet if you have already uploaded the Sample-Superstore dataset.

**Step 2: **Drag and drop the feature “Order Date” in the Columns section and the “Sub-Category” in the Rows section.

**Step 3: **Create a new calculated field.

**Step 4: **In the calculated field, first, write the formula to find the difference between the current and previous values of the “Sales” feature.

Here,

**SUM([Sales]):**This calculates the sum of the values in the [Sales] field.**ZN():**In Tableau, ZN() is used to replace NULL values with zeros. It converts a NULL value to zero, and if the value is not NULL, it returns the original value.**ZN(SUM([Sales])):**This wraps the sum of sales within the ZN() function, ensuring that if the sum of sales is NULL or empty, it will be treated as zero.**LOOKUP():**In Tableau, the LOOKUP() function is used to look up the value of an expression at a specified offset in the data partition. It is often used in table calculations.**LOOKUP(ZN(SUM([Sales])),-1):**It is used to get the previous value to the “Sales” column.

**Step 5: **To convert it to a percentage value, divide it by the previous value using the Tableau Lookup previous value method.

Here,

**ABS(): **Calculates the absolute value of a number. It ensures that the denominator (the value obtained from the previous row) is always positive, avoiding division by zero errors.

**ABS(LOOKUP(ZN(SUM([Sales])),-1)): **Calculates the absolute value of the sum of sales from the previous row.

By adding it to the Text component in the “Marks” tab, you get this (drag and drop in the “Text” tab” as shown in previous examples).

But this doesn’t have the percentage symbol.

**Step 6: **Right-click on the calculated field in the “Marks” tab and select “Format.”

**Step 7: **Select “Percentage” and select the number of decimal points you want. Here, the default value of 2 is chosen.

It is the final table.

With that, the percentage difference in Sales compared to their previous value has been identified.

#### Example #3: Calculate Moving Average

Here, using Tableau’s functionalities, you can calculate the moving average for the number of lookup values that you want. In this case, there is no need for formulae or mathematical functions. It requires almost no coding. Follow these simple steps to learn how to calculate the Moving Average.

**Step 1: **Create a new Worksheet under the Sample-Superstore dataset.

**Step 2: **Drag and Drop the “Order Date” and select the “Month” as shown, which converts it into a continuous value.

**Step 3: **Select “Profit” and place it in the “Rows” section.

Drag and drop Profit to the “Text” component in the “Marks” tab.

**Step 4: **Right-click on “Profit” and select “Add Table” calculations.

It opens a new popup where you can customize the values to your preference.

**Step 5: **Select the “Moving Calculation” and change it to “Average”.

**Step 6: **Select “Null if there are not enough values” and set the “Previous Values” to 3. Since the first three values cannot be used in the Lookup, they are declared as “Null.”

It is the final graph.

### Important Things To Note

- Understand how Tableau partitions the data when using the Lookup function.
- Specify the offset Tableau parameter carefully to ensure you’re looking up the correct value.
- Positive offsets refer to rows below the current row, while negative offsets refer to rows above the current row.
- Use the ZN() function to handle NULL values appropriately, ensuring consistent behavior in calculations.
- Don’t assume default behavior when using the Lookup function.
- Don’t forget to handle edge cases, such as situations where the offset goes beyond the boundaries of the data partition.

### Frequently Asked Questions (FAQs)

**1. What is the difference between Excel Lookup and Tableau Lookup?**

**2. What is the difference between Lookup table and Lookup?**

**3. Are there any alternatives to the LOOKUP function in Tableau?**

Yes, alternatives to the LOOKUP function in Tableau include:

• **WINDOW functions:** Compute values across a range of data.**Example:** WINDOW_SUM([Sales]) calculates a running sum of sales across a specified window.

• **PREVIOUS_VALUE function: **Returns the value from the previous row.**Example: **PREVIOUS_VALUE(SUM([Sales])) retrieves the sum of sales from the previous row.**•** **INDEX function: **Returns the position of a specified value.**Example: **INDEX() can be used to determine the rank of a product based on sales.**•** **ZN function: **Handles NULL values and replaces them with zero or a specified value.**Example:** ZN(SUM([Sales])) replaces NULL values in the sum of sales with zero.

**4. Can LOOKUP be used to create calculated fields in Tableau?**

Yes, LOOKUP can be used to create calculated fields in Tableau. See this example below.

**5. Does the LOOKUP function support filtering or aggregation?**

Yes, the LOOKUP function in Tableau supports both filtering and aggregation.

This performs both filtering and aggregation as in the values of “Quantity” of the previous year are filtered and aggregated.

### Recommended Articles

This has been a guide to What Is **TABLEAU LOOKUP**. Here we explain how to use Lookup value in different table with examples and points to remember. You can learn more from the following articles –

## Leave a Reply