--- schema_version: "1" id: 63ca88a56635da4c0a5866a5219b5c26d86d13930bed63bffd02291316ca9252 title: Lookup collection: admin version: current locale: en-us portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_integrate_recipe_join_lookup_multivalue.md canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_recipe_join_lookup_multivalue.htm&release=264.0.0&type=5 source_site: help.salesforce.com content_type: documentation content_hash: ba909dc8d6d276925ea843e5176eb2466742150a4941d2db6b863e460d872553 description: In CRM Analytics, a lookup returns all rows from the left data stream (recipe data) and only matching rows from the right data stream (lookup source). If multiple matches are found in the right data stream, you can set the lookup to return either a single row or all matching rows. To ensure that the grain of the results doesn’t change, the lookup outputs one row for each row in the left data stream. products: - CRM Analytics - Cross Cloud release: "264" --- # Lookup In CRM Analytics, a lookup returns all rows from the left data stream (recipe data) and only matching rows from the right data stream (lookup source). If multiple matches are found in the right data stream, you can set the lookup to return either a single row or all matching rows. To ensure that the grain of the results doesn’t change, the lookup outputs one row for each row in the left data stream. ### Required Editions
Available in Salesforce Classic and Lightning Experience.
Available with CRM Analytics, which is available for an extra cost in Enterprise, Performance, and Unlimited Editions. Also available in Developer Edition.
Example Our company's marketing team captures demographic data in an external data source for opportunities stored in Salesforce. To help create more targeted campaigns, the team is building a dashboard that segments our current opportunities by the customer demographics. Let’s use a lookup to add the demographic data to each opportunity record. Consider the following two data streams. The left data stream contains opportunity records. The right contains demographic data. To illustrate how the lookup handles special cases, both data streams contain unmatched rows and duplicate keys. ![The left data stream shows the opportunity ID, customer name, and opportunity amount. The right data stream shows the opportunity ID and educational level.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipeTransformations_lookup_dataStreams.png) If the lookup is configured to return a single match when multiple matches are found, the lookup produces the following results based on the matching keys. ![The new dataset contains columns from both data streams.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipeTransformations_lookup_results.png) All rows from the left, including rows without a match, are included in the results. Although Cust\_ID 1 has two matches on the right, the lookup returns only the first matching record. Also, because Cust\_ID 4 doesn’t have a match, the Cust\_id and Education Level columns are null and Average Income is 0 for that record. Unmatched dimensions are set to null. Unmatched measures are set to 0. If the lookup is configured to return all matching values when multiple matches are found, the lookup produces the following results based on the matching keys. ![The new dataset contains columns from all matched values for each left row.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipeTransformations_multivalueLookup_results2.png) A multiple-match lookup returns the same results as the single-match lookup, except for left rows that have multiple matches. Unlike a single-match lookup, a multiple-match lookup returns all matches and combines them. For dimension columns, it generates a multivalue column with all dimension values—notice how High School and Secondary School are both in the Education Level column for Cust\_ID 1. For measure columns, it adds the measure values from all matched records. For Cust\_ID 1, the Avg Income column is 98,000 (50,000 + 48,000). ![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_note.png) Note The Preview tab doesn’t show all values in a multivalue column. Instead, it shows the first value only. Keep this behavior in mind when you preview the results of a multiple-match lookup. In the example, notice that the Preview tab shows only High School in the Education Level column even though the column contains High School and Secondary School. ![The new dataset contains columns from all matched values for each left row.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_recipeTransformations_multivalueLookup_results.png) #### See Also - [Join Node: Add Related Columns of Data to the Recipe](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_recipe_join_data.htm&release=264.0.0&type=5 "Use a join node in CRM Analytics to add columns of data from related objects to existing data in a recipe. When working with CRM Analytics datasets and external objects, add a join node on the recipe canvas. When working with Salesforce objects, you can either create a join on the recipe canvas or join the data when you first create your dataset on the Add Input Data screen.")