The Tableau LOOKUP function is one of the most powerful and widely used functions for data analysis.
It is mainly used when we want to compare values between different rows, such as comparing current data with previous or next values. This function helps in identifying trends, growth, and changes over time.
In simple words, the LOOKUP function allows you to “look” at another row in your data and fetch a value from that row. It is very useful in real-world business scenarios, such as sales comparisons, profit analysis, and performance tracking.
Unlike Excel lookup functions, Tableau LOOKUP works based on the position of rows in the visualization rather than searching for a specific value.
While analyzing month-over-month sales performance in Tableau, I wanted to compare the current month’s sales with those of the previous month.
Initially, I attempted to use simple table calculations, but they didn’t allow me to refer to specific positions in the dataset, such as “previous row” or “next row.”
As a solution, I learned about the LOOKUP() function in Tableau. This function allows us to access values from different rows within the same table. This helps us to compare trends over time, calculate differences, or find moving averages.
In this tutorial, I’ll explain how the LOOKUP function works in Tableau, its syntax, and different ways you can use it in visualizations.
What is the LOOKUP Function in Tableau?
In Tableau, the LOOKUP() function is a table calculation that retrieves values from another row based on a specified position (offset). It helps you compare values across rows in a table or visualization.
Unlike normal calculations that work on individual rows of data, table calculations work on the entire visible data in your worksheet. This makes LOOKUP very powerful for analyzing trends and patterns.
Simple Definition:
LOOKUP function returns a value from another row based on the position relative to the current row.
Using the LOOKUP() function, we can perform calculations such as year-over-year change, identify trends, and find differences between record values in a dataset.
Syntax of LOOKUP Function
The syntax of the LOOKUP function is:
LOOKUP(expression, [offset])The elements that we have used in the above syntax are as follows:
- Expression: The field or calculation for look up (for example, SUM([Sales])).
- Offset: The relative position from the current row.
- 0: Current row (default)
- -1: Previous row
- 1: Next row
Understanding Offset
Offset is the most important part of the LOOKUP function.
- 0 → Current row
- -1 → Previous row
- 1 → Next row
- -12 → 12 rows before
Example:
LOOKUP(SUM([Sales]), -1)
This will return the previous row’s sales value. The function works based on row position, not value matching.
How the LOOKUP Function Works in Tableau
The LOOKUP function works on the data present in your visualization. It does not directly access raw data; instead, it uses aggregated data in your worksheet.
For example:
- If your chart shows monthly sales
- LOOKUP can compare the current month with the previous month
This makes it ideal for:
- Time-based analysis
- Trend comparison
- Growth calculations
Why the LOOKUP Function is Important
The LOOKUP function is important because it helps in:
- Comparing values over time
- Finding differences between rows
- Creating dynamic calculations
- Analyzing trends and patterns
Many business decisions depend on comparing current data with past data. LOOKUP makes this process simple and efficient.
LOOKUP() Function Use Case in Tableau
In the examples below, we will see how to use the LOOKUP() function in Tableau.
Example 1: Calculate Month-Over-Month Sales Difference Using Tableau LOOKUP()
In this example, we will illustrate the difference in sales values across various regions to calculate the Month-Over-Month Sales difference.
Now, connect Tableau with the Superstore dataset and follow the steps below.
- To create a chart view, add Order Date to Columns and set it to Month. Next, add ‘Sales‘ to the Rows. This will show a line chart for monthly sales.
- To create a calculated field, click on the Analysis tab and select Create Calculated Field.
- Enter the field label as Month-over-Month Difference and enter the formula below.
SUM([Sales]) - LOOKUP(SUM([Sales]), -1)This formula will subtract the previous month’s sales (offset -1) from the current month’s sales.
- Now, to show the calculation in the view, add the calculated field to the Label or Tooltip card in the Marks section.
In the Tableau line chart, we can see how sales increased or decreased compared to the previous month.
This way, we can use the LOOKUP() function in Tableau to calculate the difference between values in a row.
Example 2: Show Previous Month’s Sales Using Tableau LOOKUP() LOOKUP Function
In this example, we will display the previous month’s sales without calculating the difference. For this, we will create a calculated field using the LOOKUP function.
- In this chart view, first add the Order Date to the Columns and set it to Month. Next, add ‘Sales’ to the Rows.
- Create a calculated field “Next Month Sales” using the formula below.
LOOKUP(SUM([Sales]), -1)- Now, drag the calculated field to the Tooltip and Sales on the Text card in the Marks section.
This way, using the LOOKUP function, we can display the sales value from the row above in Tableau, such as the previous month’s sales.
Example 3: Display Value of Next Row Using Lookup
In the calculated field using a positive offset in the LOOKUP function. In this, we can also refer to and display the value of the next row in the dataset.
In this example, we will display the sales value of the next month, that is, the row value below the current one.
- Create a calculated field named Sales next month and enter the formula below.
LOOKUP(SUM([Sales]), 1)- To create the line chart view, add the Sales to Columns and Order date to Rows. Now, change the Order Date from Year to Month.
- Add the calculated field to the Tooltip card in the Marks section.
Now, when we hover the cursor over the month data point in the line chart, it will also display the sales for the next month.
We can also use this formula to combine with other calculations to analyze future trends or growth expectations.
For example, if we want to show the expected percentage change for the next month, we can use the formula below.
((LOOKUP(SUM([Sales]), 1)) - SUM([Sales])) / SUM([Sales])Add the calculated field to the Tooltip. To show values as percentages, click the tooltip value and select Format.
In the Format, go to the Default section and select Numbers -> Percentage.
Now, when we hover on the data points, it will show the increase or decrease in the Sales percentage of the following month.
This way, we can use the LOOKUP() function in Tableau to display the value of the next row in the dataset.
Difference Between LOOKUP and Excel VLOOKUP
| Feature | Tableau LOOKUP | Excel VLOOKUP |
|---|---|---|
| Works on | Row position | Value matching |
| Data type | Visualization data | Table data |
| Flexibility | High | Medium |
| Use case | Trend analysis | Data lookup |
Frequently Asked Questions
Q1: What is the LOOKUP function in Tableau?
It retrieves a value from another row based on position
Q2: What does offset mean?
Number of rows to move from the current row
Q3: Can LOOKUP work without aggregation?
No, aggregation is required
Q4: What is the difference between LOOKUP and WINDOW functions?
LOOKUP fetches value, WINDOW performs calculations
Q5: Why does LOOKUP return NULL?
When target row does not exist
Conclusion
The Tableau LOOKUP function is a powerful tool for comparing data across rows and analyzing trends. It helps in understanding changes over time, making it very useful for business analysis and reporting.
By using LOOKUP correctly, you can create dynamic dashboards, perform advanced calculations, and gain valuable insights from your data.
Whether you are a beginner or an experienced Tableau developer, mastering the LOOKUP function will greatly improve your data analysis skills.
You may also like to read:
- Convert Data Type in Tableau
- Order of Operations in Tableau
- Create Groups in Tableau
- Level of Detail Expressions in Tableau
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.
