LOOKUP (+video)

Overview

LOOKUP is used to combine data, typically from two different data sources. Specifically, it uses data from one data source as an index into another and then returns a corresponding value.

Watch the following video to learn more:

Using LOOKUP

LOOKUP(input, keys, results)

  • input: an array of data to be used as indices into another data source (keys).
  • keys: an array of data that intersects with input data.
  • results: an array of corresponding data from data source 2 (may be manipulated by a formula). Must contain the same number of items as keys.

For each item in input, search for a match in keys and return the corresponding item from results

Note: If multiple keys match an item in input, only the result corresponding to the first matching key will be returned.

What's in this article?

One data source has information on a sales rep and which client they are managing. The second data source has information on the manufacturer, the country, revenue and deals won. Our goal with the LOOKUP function is to create a table with the client, country, revenue and deals won for each sales rep. 

You can download the data sources used in this article from Lookup(1) data source and Lookup(2) data source.

Hover over the image below to see the two data sources.

Use LOOKUP to find text

In this section, you will use the intersecting client data from both example data sources to accurately display all the data in one table. In the component tree, select Country.

LOOKUP(SLICE(values), keys, results)

  • values: set to the client data, column B (B:B), from the Lookup(1) data source.
  • keys: set to the client data, column A (A:A), from the Lookup(2) data source.
  • results: set to the countries, column B (B:B), of the Lookup(2) data source.

Use LOOKUP to group values together 

You can use GROUP and GROUPBY to group the revenue assigned to each client and display it in a table. This example is used in the Revenue sub-component.

LOOKUP(SLICE(values1), GROUP(values2), GROUPBY(values2, measure))

  • values1: set to the client data, column B (B:B), from the Lookup(1) data source.
  • values2: set to the client data, column A (A:A), from the Lookup(2) data source.
  • measure: set to the revenue data, column C (C:C), from the Lookup(2) data source

What's next?

Did you have trouble with the material in this article?

These articles may help: