Lookup Function in Tableau - [Understand Lookup with 3 Examples]

What is LOOKUP Function in Tableau?

The LOOKUP function in Tableau, which is used to look up a value in previous or subsequent row in a table or partition. The LOOKUP in Tableau function can be used to calculate running totals, moving averages, and other types of calculations that require access to previous or subsequent values in a table or partition.

Here is the syntax of the LOOKUP() function in Tableau tool:

LOOKUP(expression, offset)

Understand the function arguments/expression:

Expression is the expression to be evaluated
Offset is the number of rows before or after the current row to use for the lookup. The offset value can be positive or negative.

What is the ZN lookup function in Tableau?

There is two different function combines to make ZN Lookup function, ZN function used to replace Null vales with Zero and LOOKUP used to look-up a value in a previous or subsequent row in a table.

So what happen when there is any Null vales in field that replaced by Zero with the help of ZN function.

Lets take example below, here ZN replace the Null values with zero if there is Null on Sum level.

= LOOKUP(ZN(SUM([Sales]), -1)))

What is lookup table vs lookup Function?

Below are the main difference between Lookup table and Lookup

Lookup Table	Lookup Function
Lookup table is the method where you can perform task at table level meaning you can show differences and calculation only numbers of rows available in particular table and chart view.	Lookup is the Function or formula used to calculate with offset to in calculate field which can be used any columns and vies withing the dashboard.
This is the manually applied method or calculation on a particular table	This is the Formula based calculation
Its perform computations on values in a table	Its perform computations on row levels of data source

Key features of the LOOKUP() function:

Can be used to look up values in previous or subsequent rows in a table or partition

Can be used to calculate running totals, moving averages, and other types of calculations that require access to previous or subsequent values in a table or partition

It can be used with dimensions or measures

It can be used with discrete or continuous fields

It can be used in calculated fields, table calculations, and level of detail expressions

Lets try to understand more with some examples of the LOOKUP() function in Tableau tool with syntax:

Example #1: Calculate Moving Average using Lookup Function

Calculate the moving average of last 3 years of [Total Cost] by looking up the average of the previous 3 years values of [Total Cost] and adding it to the current value.

You can use below calculated formula with Lookup in Tableau.

=(SUM(LOOKUP(SUM([Sales]), -1)) + SUM(LOOKUP(SUM([Sales]), -2)) + SUM(LOOKUP(SUM([Sales]), -3)) + SUM([Sales])) / 4

The out put will be look like as show below screenshot. The third column shows how the average is calculating its taking average of last 4 years.

Output of moving average using Lookup in tableau

Example #2: Calculate Percentage change using Lookup

Calculate the percent change in sales by looking up the previous value of sales and calculating the percent difference. In below formula it is calculated with the help of Lookup function in Tableau

=(SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / LOOKUP(SUM([Sales]), -1)

The result will look like as shown below image

Output of percentage change using Lookup in tableau

The result will show the percentage in decimal integer, you can change this into percentage by following below steps

Right click on Calculated filed from “measures values” are and then click on Format option as shown below image

Now click on Pane option > the numbers > the percentage and select decimal places for percentage, as shown below image

Option to change percentage format in tableau

Example #3: Calculate difference between current and previous values using Lookup

Calculate the difference between the current value of sales and the average of the previous three values of sales. Here you need to use average lookup formula and simple SUM formula to calculate these see the below calculated formula.

= SUM([Sales]) - (SUM(LOOKUP(SUM([Sales]), -1)) + SUM(LOOKUP(SUM([Sales]), -2)) + SUM(LOOKUP(SUM([Sales]), -3))) / 3

Below is the output image , which calculate difference between current values and the average of the previous three values of sales

These examples demonstrate how the LOOKUP() function can be used to look up values in previous or subsequent rows in a table or partition.

And how it can be used to calculate difference, averages and other types of calculations. The LOOKUP in Tableau is a powerful function for performing complex calculations in Tableau.