LOOKUP function

What's in this article?

  • Learn how to use the LOOKUP function to create a Klip.
  • Step-by-step instructions.

How to use the LOOKUP function

The LOOKUP function is used to combine data, typically from two different data sources, specifically to use data from one data source as an index into another and then return a corresponding value. 


Where

  • 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 datasource2 (may be manipulated by a formula). Must contain the same number of items as Keys.

For each value in Input, search for a match in Keys and return the corresponding value from Results

NOTE: If multiple Keys match a value in Input, only the Result corresponding to the first matching Key will be returned.

To use the LOOKUP function

The LOOKUP function is useful for correlating information from two data sources, as in the following example.


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 be able to see the client, country, revenue and deals won for each sales rep. The following shows 3 different ways to use the LOOKUP function.



You can download the data sources used in this article from 
Lookup1 data source and Lookup2 data source.

Method 1 - Look up and find text

  1. Click  Select data or start formula and click Insert function.
  2. Select Data Manipulation > LOOKUP.
  3. Select the input element, then select values B:B from the Lookup1 data source.
  4. Select the keys element, then select values A:A from the Lookup2 data source.

  1. Select the results element.
  2. Select values B:B from the Lookup2 data source.


Method 2 - Look up and group values together 

This method uses the GROUP and GROUPBY functions.

  1. Click Select data or start formula and click Insert function.
  2. Select Data Manipulation > LOOKUP from the function library.
  3. Select the input element, then select B:B from the Lookup1 data source.
  4. Select the keys element, the click Insert function and select Data Manipulation > GROUP from the function library.
  5. Select A:A from the Lookup2 data source.

  1. Select the results element and click Insert function.
  2. Select Data Manipulation > GROUPBY from the function library.
  3. Select A:A from the Lookup2 data source.
  4. Select the measure element of the GROUPBY function, then select C:C from the Lookup2 data source.


Method 3 - Lookup, group values together and use an IF statement to summarize values 

This method uses the GROUP/GROUPBY, and IF functions.

  1. Click Select data or start formula link and click Insert functionSelect Data Manipulation > LOOKUP.
  2. Select the input element, then select B:B from the Lookup1 data source.
  3. Select the keys element, then click Insert functionSelect Data Manipulation > GROUP.
  4. Select A:A from the Lookup2 data source.
  5. Select the results element and click Insert functionSelect Data Manipulation > GROUPBY.
  6. Select A:A from the Lookup2 data source.
  7. Select the measure element of the GROUPBY function and click the Insert functionSelect Logic > IF.

  1. Click Insert expression in parenthesis and select D:D from the Lookup2 data source.
  2. Select the ellipsis (...) beside the D:D element and select the = operator.
  3. Select the ellipsis (...) beside the equals sign and click Insert literal string or number .
  4. Type yes in the text field and click Apply.

  1. Select the if true element and click Insert literal string or number and type 1 in the text field. Click Apply.
  2. Select the if false element and click Insert literal string or number and type 0 in the text field. Click Apply.

What's next?

Did you have trouble with the material in this article?

These articles may help:

Functions

  1. Introduction: Using formulas
  2. Data Manipulation functions
  3. Logic functions
  4. Math functions
  5. SELECT function
  6. Text Functions
  7. Date and Time Functions
  8. Statistics Functions
  9. ABS function
  10. AND function
  11. ARRAY function
  12. AVERAGE function
  13. BETWEEN function
  14. BLANK function
  15. CAPITALIZE function
  16. CEILING function
  17. CONCAT function
  18. CONTAINS function
  19. COUNT function
  20. COUNTALL function
  21. COUNTBLANK function
  22. COUNTDISTINCT function
  23. COUNTIF function
  24. COUNT_DAYS function
  25. COUNTNUMERIC function
  26. CUMULATIVE function
  27. CUMULATIVE_DIFFERENCE function
  28. DATASOURCE function
  29. DATE and DATEVALUE functions
  30. Using Date and Time Formats
  31. DATERANGE function
  32. DATE_ADD function
  33. DATE_CLOSEST function
  34. DATE_CONVERT function
  35. DATE_ENDOF function
  36. DATE_IN function
  37. DATE_SET function
  38. DATE_STARTOF function
  39. DATE_UNITVALUE function
  40. FLOOR function
  41. ERF function
  42. FIRST function
  43. ERFC function
  44. GROUP and GROUPBY functions
  45. IF function
  46. IN function
  47. INDEXOF function
  48. JOIN function
  49. LAST function
  50. LASTINDEXOF function
  51. LEFT function
  52. LOOKUP function
  53. LOWER function
  54. MAP function
  55. MAX function
  56. MIN function
  57. MEDIAN function
  58. MOD function
  59. MODE function
  60. Moving Average (Cumulative)
  61. Moving Average (Exponential)
  62. Moving Average (Simple)
  63. NORMSDIST function
  64. NOT function
  65. NOW function
  66. NUMBERFORMAT function
  67. OR function
  68. PADVALUES function
  69. POWER function
  70. RANK function
  71. REMOVE_EMOJI function
  72. REPEAT function
  73. REPLACE function
  74. REVERSE function
  75. RIGHT function
  76. ROUND function
  77. SET function
  78. SLICE function
  79. SLOPE function
  80. SORT function
  81. SPLICE function
  82. STANDARDIZE function
  83. STDEV function
  84. STDEVP function
  85. SUBSTITUTE function
  86. SUBSTITUTE_REGEX function
  87. SUBSTRING function
  88. SUM function
  89. SUMIF function
  90. SWITCH function
  91. TEXT_REVERSE function
  92. TODAY function
  93. TRIM function
  94. TRUNCATE function
  95. UPPER function
  96. URLDECODE function
  97. URLENCODE function
  98. VARIANCE function
  99. VARIANCEP function
  100. YESTERDAY function
  101. ZTEST function

Feedback and Knowledge Base