When importing data into Method, it is common to have a Contact's Record ID as a column to populate. In the below import example, the Contacts column needs to have the Record ID of the contact beside it.
However, you don't want to input this column in by hand. Even if you were copy and pasting, there is a lot of room for error.
It would be handy if the Contacts column (and the Entity column as well) were automatically populated when we enter in a ContactName.
This can be done by using the VLOOKUP function which is available in any spreadsheet program, such as Excel or Google Spreadsheets.
However, you first need a primary 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.
Step 1: Export Your Contacts Table
Click the Profile icon in the upper right and click Integrations.
Click Export.
On the Export screen, change the Table to the Contacts.
You only need three fields for this export. So before choosing the Export fields, click the Deselect all fields link.
Select the following fields:
RecordID
Entity
Name
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.
Import Contacts.csv into your import template as a separate spreadsheet. In Google Sheets, this will create a separate tab as shown below:
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.
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.
=vlookup(A2,Contacts!$A:$C,2,true)
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.
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.
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:
=vlookup(A2,Contacts!$A:$C,3,true)
The only thing that has changed in this is the third parameter, the "3", which requires the function to look in the third column which contains the customer name.
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. Make sure you save right spreadsheet! You don't want to accidentally export the contacts.csv file.