How to Use LOOKUP in Tableau

Performing comparisons over time is the bedrock of business analysis, but doing it in Tableau often requires a specific tool: the LOOKUP function. This powerful table calculation lets you “look” into other rows in your visualization to compare values, making it perfect for things like year-over-year growth or percent change from the previous month. This guide will walk you through exactly how the LOOKUP function works and show you how to apply it with practical, step-by-step examples.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is the LOOKUP Function in Tableau?

In simple terms, the LOOKUP function retrieves a value from a different row or "offset" in your current view. Think of your visualization as a table of data. LOOKUP lets you reach forward or backward into other cells in that table to use them in a calculation.

Because it operates on the data visible in your sheet, it's known as a Table Calculation. This is an important distinction: unlike a regular calculation that works at the row-level of your dataset, a table calculation only works with the aggregated data you’ve placed in your view.

The LOOKUP Syntax

The formula for LOOKUP is refreshingly simple:

LOOKUP(expression, [offset])

Let's break that down:

• expression: This is the value you want to retrieve. It's usually an aggregated measure, like SUM([Sales]) or AVG([Profit]).
• [offset]: This is the number that tells Tableau how many rows to move from the current position.

The offset is the key piece of the puzzle. Here's how it works:

• A negative offset (e.g., -1) looks at a previous row. LOOKUP(SUM([Sales]), -1) gets the sales from the previous mark in your view.
• A positive offset (e.g., 1) looks at a subsequent row. LOOKUP(SUM([Sales]), 1) gets the sales from the next mark.
• A zero offset (e.g., 0) looks at the current row. LOOKUP(SUM([Sales]), 0) returns the sum of sales for that same mark. While a bit redundant for this example, it's very useful for advanced filtering techniques.

For example, if you have a chart showing monthly sales, LOOKUP(SUM([Sales]), -12) will look back 12 marks (or 12 months) and return the sales value from the same month last year.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Common Use Cases for LOOKUP

So when would you actually need to use this function? Here are a few of the most popular scenarios:

• Year-over-Year (YoY) Growth: The classic use case. You need to compare sales, revenue, or user signups in a given month to the same month in the prior year.
• Period-over-Period Changes: Similar to YoY, but for shorter timeframes. This is great for calculating Month-over-Month (MoM) or Week-over-Week (WoW) performance changes.
• Finding the Difference From a Target: If your goals are set on a periodic basis, you could use LOOKUP to compare current performance against a previous period that acts as a benchmark.
• Creating Moving Averages Manually: While Tableau has a WINDOW_AVG function for this, you could build one yourself with a series of LOOKUPs to understand the underlying mechanics of window functions.

Step-by-Step Guide: Building a Year-over-Year Growth Report

Theory is great, but let's put LOOKUP into practice. We'll build one of the most requested types of reports: Year-over-Year (YoY) Sales Growth. For this example, we’ll use Tableau's Sample Superstore dataset.

Step 1: Set Up the Initial Chart

First, we need a view that shows our sales trends over time. A line chart is perfect for this.

1. Drag Order Date to the Columns shelf. Right-click the pill and make sure it is set to the second Month option (MY(Order Date)), which will display a continuous axis of months and years.
2. Drag Sales to the Rows shelf. Tableau will automatically aggregate this as SUM(Sales).

You should now have a line chart that shows total sales for each month across all years in the dataset.

Step 2: Calculate the Previous Year's Sales

Now, we need to create a calculated field that uses LOOKUP to find the sales value from 12 months ago.

1. Click the drop-down arrow in the top-left of the Data pane and select Create Calculated Field.
2. Name the calculation Previous Year Sales.
3. Enter the following formula:

LOOKUP(SUM([Sales]), -12)

Click OK. We've just instructed Tableau to find the sum of sales but to look back 12 steps (months) in our visualization. For January 2022, this calculation will return the value from January 2021.

If you want to validate this, you can drag your new Previous Year Sales field onto the Rows shelf to see both lines together. You'll notice they are identical, just offset by one year.

Step 3: Calculate the Year-over-Year Growth Percentage

With both current and previous sales available, we can now calculate the growth percentage. The standard formula for this is: (Current Period - Previous Period) / Previous Period.

1. Create another new calculated field.
2. Name it YoY Growth %.
3. Enter the formula:

(SUM([Sales]) - [Previous Year Sales]) / [Previous Year Sales]

Click OK.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Visualize the Growth Calculation

Let's add our YoY growth calculation to the chart. You could create a second chart, but a great way to show positive and negative growth is by using color.

1. Remove the Previous Year Sales pill from the Rows shelf if you added it.
2. Drag your new YoY Growth % field onto the Color tile on the Marks Card.
3. Right-click the YoY Growth % pill that just appeared on the Marks Card, go to Format..., and in the pane that opens on the left, select Numbers and choose Percentage.
4. You can also click on the Color tile and select Edit Colors to pick a more intuitive color palette, like a red-green diverging one, which visually separates periods of growth from periods of decline.

You'll notice that the entire first year on the chart is a single neutral color. This is expected! There is no "previous year" data for the first 12 months, so the LOOKUP function returns NULL, and no percentage can be calculated.

Shortcut: Using Quick Table Calculations

For common calculations like period-over-period differences, Tableau offers a fantastic shortcut that uses LOOKUP behind the scenes. Let's calculate the Month-over-Month difference in sales:

1. Create a new sheet.
2. Drag Order Date to Columns (keep it as MONTH(Order Date)) and SUM(Sales) to Rows. A line chart is fine.
3. Right-click on the SUM([Sales]) pill on the Rows shelf.
4. Navigate to Quick Table Calculation > Percent Difference.

Tableau instantly changes the chart to show the percentage change from the previous month. That's it! To see the formula Tableau generated, you can hold Ctrl (or Cmd on Mac) and drag the pill from the Rows shelf into the Data pane. It will save as a new calculated field, and if you edit it, you’ll see it’s using a LOOKUP function similar to this:

(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

This version includes ZN() to gracefully handle NULLs (by turning them into zeros) and ABS() to prevent division by a negative number - small improvements that make the calculation more robust.

Tips and Common Mistakes to Avoid

The LOOKUP function is powerful, but it can be tricky. Here are a few common issues and tips to keep in mind.

1. The "Compute Using" Setting is Everything

This is by far the most common source of confusion. The LOOKUP(SUM([Sales]), -12) formula needs to know how it should count back 12 steps. Across the entire chart? Down a column? Within a specific pane?

• How to fix it: Right-click your table calculation pill (like our YoY Growth %) and choose Edit Table Calculation.... This opens a dialog box where you can change the "Compute Using" setting. For our yearly growth chart, Table (across) is the default and correct setting. But if your view was more complex, you might need to compute using a specific dimension like Order Date. Always check this setting if your results look wrong.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Handling NULL Values at the Beginning

As we saw, a LOOKUP will always return NULL for the first few marks where there's no prior data to look at (e.g., the first 12 months in a YoY calculation). This is normal, but sometimes you may want to display a zero instead.

• How to fix it: Wrap your LOOKUP function in a ZN() function. For example: ZN(LOOKUP(SUM([Sales]), -12)). This will convert any NULLs into a 0.

3. The Impact of Filtering

Because LOOKUP is a table calculation, it happens late in Tableau's order of operations. Standard filters remove data before the calculation runs, which can mess up your LOOKUP offsets.

• How to work around it: If you need to filter the view without altering the underlying data used in the LOOKUP, use a Table Calculation Filter. For example, to hide certain months while preserving the YoY calculation, you could create a calculated field like LOOKUP(ATTR(MONTH([Order Date])), 0) and use that as an interactive filter in the view. Table Calculation Filters are applied last, keeping the data for other table calculations intact.

Final Thoughts

Mastering Tableau's LOOKUP function is a major step toward creating more sophisticated and insightful analyses. It’s your primary tool for turning a simple business-over-time chart into a powerful dashboard demonstrating growth, decline, and performance against prior periods. The key is understanding its simple syntax and paying close attention to the "compute using" settings.

While mastering functions like LOOKUP is a valuable skill, it often comes with a steep learning curve and hours of manual report building. We found that marketing and sales teams often need these answers quickly, without becoming Tableau experts. That's why we built Graphed. Instead of wrestling with calculated fields and complex settings, you can simply ask, "Show me our year-over-year sales growth by month," and instantly get an interactive, real-time dashboard. It's the fastest way to get insights from your data, freeing you up to focus on strategy, not setup.