Lookup()

One of the most useful functions of Excel is the VLookup and its lesser known cousin the HLookup these allow an analyst to use information from one table in another. Tableau has a similar function, that is the lookup() function, however rather than being used to lookup data from another table lookup allows for data from elsewhere in a table to be used in a calculation.

Lookup() is a table calculation and looks at dat based on the partitioning and addressing scheme used, (our blog post on table calculations has more information on this) Lookup has two parts , the first part is the measure or dimension to return, and the second is where the function will look to get the value described in the first part. This can either be a relative reference for example look -1 space back in the table or 1 space along in the table.

Even better you can combine lookup() with last() to allow you to find the first or last elements in a table and even use first()+n or last()-n to look relative to the first and last positions.

A couple of quick examples:

Heres a quick table of profits over years

Year Profit
2000 £30m
2001 £32m
2002 £41m
2003 £45m

Lookup relative to the current position in the table.

LOOKUP(SUM([Profit],-1) gives us:

Year Profit
2000  
2001 £30m
2002 £32m
2003 £41m

Lookup on the first value of the table.

Lookup(SUM([Profit],first()) gives us:

Year Profit
2000 £30m
2001 £30m
2002 £30m
2003 £30m

Lookup is an incredibly versatile tool and well worth practice, and is probably the table calculation I've used most often.

We hope you have found this mini-series on table calculations useful. and please stay tuned for more #TodaysTableauTips.

Free Alteryx Trial!

Free Tableau Trial!

Get in touch with us today!

Contact us
Cookies make it easier for us to provide you with our services. With the usage of our services you permit us to use cookies.
More information Ok