VLOOKUP in Excel to Prep Your Data Import

A VLOOKUP in a spreadsheet is much like a lookup field in the CRM or Books (or any other app, really) or a JOIN in SQL.  The basic idea to understand is that VLOOKUPs depend on finding a matching unique value between two different arrays of data. In our case, this is the Account ID. Watch the video and use this link to learn how they work. Better yet, try it yourself!

Here is the basic syntax:

=VLOOKUP(value_to_look_for, array_in_which_you_search_for_the_value, column_number_to_return, TRUE/FALSE)

The value to look for should be the unique value from the module to which you’re looking to add data

The array in which you search for the value should have that unique value in the left-most column and the value you want to return somewhere to the right of that column.

TRUE means you are ok looking for an approximate match

FALSE means you only want to return exact matches (when running VLOOKUPS based on record IDs, always choose FALSE)

Line up the columns correctly. Your Account ID from the Accounts module should be in the left-most column of your array and the Customer Type column to the right of that.

Your value to search for is the Account ID from the Contacts module. If your Contacts-module Account ID is in column E and your array to match on is in columns I through L, your formula should look something like this:

=VLOOKUP($E2, $I$2:$L$5000, 4, FALSE)

The values in E are what we’re looking for in I. Then, because L, the column we want to return, is the 4th column in the array, we put a “4” as our third parameter in the function. Last, we want an exact match, so we put “FALSE”.

Copy that formula all the way down and it’s pretty easy to verify it worked correctly!

Back to Lesson