When importing data into Method:CRM, it is common to have a Contact's Record ID as a column to populate.
However, this is hard to set up because we normally identify people by their name, not by their Record ID number. Furthermore, the customer of the contact is normally a required column, and ensuring everything is properly matched can be complicated.
The below image is a screenshot of an activities import file in a Google spreadsheet:
Wouldn't it be handy if the Contacts and Entity column were automatically populated when we enter in a ContactName?
You can do this by using the VLOOKUP function which is available in any spreadsheet program, such as Excel or Google Spreadsheets.
However, you first need a master cross-reference spreadsheet. You can create this by exporting your contacts table which will result in a file similar to the one pictured below:
The following steps in this article will show you how to:
- Export your Contacts Table
- Use the VLOOKUP function
Of course, this only works on existing contacts. You will not be able to look up a Record ID nor a customer name if the contact does not exist in Method:CRM.
Step 1: Export Your Contacts Table
1. Click the blue icon in the upper right corner of the screen and choose Integrations.
2. Click Launch Import & Export.
3. The Import screen will come up and you will be taken to the first step. Click the link Classic Import Tool.
4. In the Import / Export tool, select Export a table to a file.
5. By default, the list only contains "common tables", and the Contacts Table isn't part of that. Uncheck Show only common tables? to see all tables. Then select the Contacts table from the dropdown.
6. You only need three fields for this export. So before choosing the Export fields, click the Unselect All button.
7. Select the following fields:
8. Click the button Export to file. This will download a Contacts.csv file which contains all your contacts. You will need this in the next section.
Step 2 - Use the VLOOKUP Function
Once you've downloaded your Contacts.csv file from the first step, you will need to bring it into your current import template.
1. Import Contacts.csv into your import template as a separate spreadsheet. In Google Sheets, this will create a separate tab as shown below:
2. If not already done, move the Name column to the first column (A), and then sort the Name column, making sure to keep the headers intact.
3. Go to the tab which has your import template. In this example, we will use an activity import template. Find the column for the Contacts field and type this function in for the 2nd row of the contact.
This will lookup the ContactName in the Contacts spreadsheet, and return the appropriate Record ID! Let's take a look what this formula means:
- =vlookup: This starts the vlookup function off. Every function starts with an '=' sign.
- A2: The first parameter for this function needs a field to base the lookup on. In other words, if I were to look up a word in the dictionary, I need to know what the word is to look up. In the above example, A2 indicates we want to look up "Amy Ford".
- Contacts!$A:$C: The second parameter tells the function where to look this up. "Contacts!" indicates it's in another tabbed spreadsheet labeled "Contacts". "$A:$C" indicates to look from column A to column C. (The dollar signs mean the columns are anchored, which is beyond the scope of this article.)
- 2: The third parameter is a number and tells the function which column to look in order to return that row value. In our example, it will look in the 2nd column, which is column "B".
- true: The fourth parameter is whether or not the Contacts are sorted. Setting this to false will return an exact match and doesn't require the contacts to be sorted.
4. Copy and paste this function for the rest of column B, so that every contact will have a proper Record ID:
Note how the first parameter changes on each row.
5. Now you can do the same thing for the Customer. In this example, this refers to the column Entity. Select the blank entity in the second row and type:
The only thing that has changed in this is the third parameter, which requires the function to look in the third column which contains the customer name.
6. Copy and paste this function for the rest of column C, so that every contact will have their associated customer:
And there you go! Using VLOOKUP, you can ensure the Contact will have their proper Record ID and customer name.
Once you save this import template file, you can import that template into Method:CRM. Make sure you save right spreadsheet! You don't want to accidentally export the contacts.csv file.