web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / How to map a lookup on...
Microsoft Dynamics CRM (Archived)

How to map a lookup on multiple fields in Data Import Wizard

(0) ShareShare
ReportReport
Posted on by Community Member

I need to import a data set that contains a lookup field (e.g. column-A). However, the values in column-A are not always unique in CRM. As a result, I always got import error "A duplicate lookup reference was found". In this data set, there is no any single column that can be used as a unique reference for this lookup. However, there is a combination of two columns (e.g. column-A and column-B) that can uniquely determine the lookup reference.

My question is how to set up the lookup based on the combination of both column-A and column-B in data import wizard?

Thanks in advance!

Hao

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Learn@mscrm Profile Picture
    Learn@mscrm 50 on at
    RE: How to map a lookup on multiple fields in Data Import Wizard

    Hi Aileen,

    I'm facing similar issue. I have to import 5000+ records in my application. we are using Dynamics 365 online version.

    i have totally 4 entities and are related. I have to import one entity which has look up value. The look up values are same values and I'm getting duplicate reference error.

    four columns

    A B C D

    A: B = 1: N

    B: C  = 1 : N

    C:D = 1: N

    c values are many so when i import i have duplicate reference error,  but if i combine B and C it is unique.

    I created a combination field(single line of txt) in "C" entity

    I tried to map this combination field to look up entity, Im able to import D values but column C is blank.  Can you tell me how to import look up values(C) which should be related to B

    Column  Column

    D               C

    value 1      .......

    value 2      .......

    from checking related material i did following ways

    D          C           B               Combination (using excel formula and dragged it)

    D1          C1          B1             B1-C1

    D2          C1          B2             B2-C1

    D3          C1          B3              B3-C1

    D4          C1          B4              B4-C1

    D5          C1          B5              B5-C1                      

    D entity had two fields Name(text) and C value(look up)

    Can you please help mapping and let me know steps to do this import. let me know if you need any other details.

  • Learn@mscrm Profile Picture
    Learn@mscrm 50 on at
    RE: How to map a lookup on multiple fields in Data Import Wizard

    Hi Aileen,

    I'm facing similar issue. I have to import 5000+ records in my application. we are using Dynamics 365 online version.

    i have totally 4 entities and are related. I have to import one entity which has look up value. The look up values are same values and I'm getting duplicate reference error.

    four columns

    A B C D

    A: B = 1: N

    B: C  = 1 : N

    C:D = 1: N

    c values are many so when i import i have duplicate reference error,  but if i combine B and C it is unique.

    I created a combination field(single line of txt) in "C" entity

    I tried to map this combination field to look up entity, Im able to import D values but column C is blank.  Can you tell me how to import look up values(C) which should be related to B

    Column  Column

    D               C

    value 1      .......

    value 2      .......

    from checking related material i did following ways

    D          C           B               Combination (using excel formula and dragged it)

    D1          C1          B1             B1-C1

    D2          C1          B2             B2-C1

    D3          C1          B3              B3-C1

    D4          C1          B4              B4-C1

    D5          C1          B5              B5-C1                      

    D entity had two fields Name(text) and C value(look up)

    Can you please help mapping and let me know steps to do this import. let me know if you need any other details.

  • Learn@mscrm Profile Picture
    Learn@mscrm 50 on at
    RE: How to map a lookup on multiple fields in Data Import Wizard

    Hi Aileen,

    I'm facing similar issue. I have to import 5000+ records in my application. we are using Dynamics 365 online version.

    i have totally 4 entities and are related. I have to import one entity which has look up value. The look up values are same values and I'm getting duplicate reference error.

    four columns

    A B C D

    A: B = 1: N

    B: C  = 1 : N

    C:D = 1: N

    c values are many so when i import i have duplicate reference error,  but if i combine B and C it is unique.

    I created a combination field(single line of txt) in "C" entity

    I tried to map this combination field to look up entity, Im able to import D values but column C is blank.  Can you tell me how to import look up values(C) which should be related to B

    Column  Column

    D               C

    value 1      .......

    value 2      .......

    from checking related material i did following ways

    D          C           B               Combination (using excel formula and dragged it)

    D1          C1          B1             B1-C1

    D2          C1          B2             B2-C1

    D3          C1          B3              B3-C1

    D4          C1          B4              B4-C1

    D5          C1          B5              B5-C1                      

    D entity had two fields Name(text) and C value(look up)

    Can you please help mapping and let me know steps to do this import. let me know if you need any other details.

  • Suggested answer
    Abarika Profile Picture
    Abarika 5 on at
    RE: How to map a lookup on multiple fields in Data Import Wizard

    This is just one of the limitations of the VLOOKKUP function. 

    Conventionally, it cannot work with two criteria. It cannot look into two columns and return a value based on those columns.

    However, there's a technique I use to get around this restriction - Using a helper column.

    • First, you create a new column in your table as your helper column.
    • Next, use the =CONCATENATE() function to join the two columns you want to use as lookup values. See screenshot:two-column-lookup2.png
    • Now use the helper column as your lookup column to perform the VLOOKUP
    • two-column-lookup2.png

  • KATZH Profile Picture
    KATZH on at
    RE: How to map a lookup on multiple fields in Data Import Wizard

    Hi

    I have a similar problem. I have a lookup reference on "household" (custom entity) in my notes to be imported. My household exists 2 times: 1 inactive old record (to be kept) and an active record. I want to import the notes to my active households.

    I always get a duplicate error.

    How do I have to prepare my Notes Import Excelsheet / CSV / XML to reference on 2 values: The ID of the household AND the Status.

    My columns are Title / Description / ID / Status

    I cannot export the records to get the GUID. I have to import Notes REGARDING Household by using the ID and Status.

    regards

    Kaida

  • Community Member Profile Picture
    Community Member on at
    RE: How to map a lookup on multiple fields in Data Import Wizard

    Thanks Aileen,

    This is very helpful.

    Hao

  • Verified answer
    Aileen Gusni Profile Picture
    Aileen Gusni 44,524 on at
    RE: How to map a lookup on multiple fields in Data Import Wizard

    Hi Hao,

    No problem.

    Yes, this is one of the limitation of its 'combination'

    you can use GUID..

    But little bit tricky...

    Export the reference dataset, for example in my case is Account...(don't forget to check the Re-import capability) in order to get Account ID.

    Then the difficult part here is how to do the mapping.

    You need to open at least two excel sheet, one is your CSV, one is the Master reference you downloaded from the previous step. And I guess you also need another new CSV as the new combination.

    Now you have two fields (the original CSV + Master reference)

    Now, you need to use VLOOKUP with multiple criteria or concatenation

    Example here:

    www.excel-university.com/vlookup-on-two-or-more-criteria-columns

    exceltactics.com/faster-multiple-criteria-lookups-vlookup-concatenate

    so here you can find the GUID of the Account based on this VLOOKUP.

    I know this is manual way..

    But, no choice for this method.

    Hope that helps you, Hao.

    Thanks.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: How to map a lookup on multiple fields in Data Import Wizard

    Hi Aileen,

    Thank you very much for the explanation. It is very comprehensive and clear. I really appreciate it.

    My scenario is like the last one. The uniqueness for lookup relies on multiple references.

    I guess that I'll have to create a new field in CRM as the unique reference for importing data.

    Thanks again,

    Hao

  • Verified answer
    Aileen Gusni Profile Picture
    Aileen Gusni 44,524 on at
    RE: How to map a lookup on multiple fields in Data Import Wizard

    Hi Hao,

    So yes, it is not possible as per my explanation before.

    You need to use GUID as the only one unique column.

    Thank you.

  • Community Member Profile Picture
    Community Member on at
    RE: How to map a lookup on multiple fields in Data Import Wizard

    Thanks Chitra,

    I read that article, but it won't solve my issue because in my data set I don't have any single column that can be used as a unique reference.

    Thanks,

    Hao

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Our Community Code of Conduct has been updated!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
Aric Levin - MVP Profile Picture

Aric Levin - MVP 2 Moderator

#2
MA-04060624-0 Profile Picture

MA-04060624-0 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans