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.
The syntax for LOOKUP is:
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.
Watch the following video to learn more:
Using LOOKUP
This article gives an example of how you can use LOOKUP in Klipfolio to find text and group values together.
You can download the data sources used in this article from Lookup(1) data source and Lookup(2) data source. The first 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.
For this example, create a Table component with the following headings: sales rep, client (car makes), country, revenue and deals won.
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(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
In this section, you will use GROUP and GROUPBY to group the revenue assigned to each client and display it in a table. In the component tree, select Revenue.
LOOKUP(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.
The final table will look like the following:
