Home → Campaign Manager → Frequently Asked Questions! → How do I display record names in a Final .MDB Export Excel sheet?
1.17. How do I display record names in a Final .MDB Export Excel sheet?
How do I display record names in a Final .MDB Export Excel sheet?
This HelpFile will walk you through the process of matching names in your Final Export .MDB Excel table to their GUID number. You must first convert your Final Export .MDB file into Excel tables. Once you have converted your file into Excel, you'll notice that the records within the spreadsheets are identified by their GUID number. You can use Excel's VLOOKUP tool to match GUID numbers to the name data on another spreadsheet. In addition to the following step-by-step instructions, here is a link to a short video on how to use Excel's VLOOKUP tool.
VLOOKUP function basics:
The VLOOKUP function looks for a value [lookup_value] in the leftmost column of a table [table_array], and returns a value in the same row from a column you specify [col_index_num]. By default, the columns must be sorted in ascending order. The [range_lookup] returns an approximate match if you type in the word "true," or an exact match if "false."
VLOOKUP definitions:
- [lookup_value] is the value you are looking up.
- [table_array] is the selection of cells you want Excel to match to or search by. The leftmost column is what's being matched
- [col_index_num] corresponds to the column within the [table_array] you want displayed. To display Column A, type "1." Column B=2, Column C=3, etc.
- [range_lookup] is either the word TRUE or FALSE. Type TRUE for an approximate match, and FALSE is an exact match.
Step-by-step instructions using individual records as an example:
1 . Find and make note where your tab with all the individual records name information. This will have ind_GUID, ind_FirstName, ind_LastName, etc. as Column headers. This will be the information we'll be using in Step 5 as the [table_array] selection.
2. Now go to the sheet to which you'd like to match the name information. For this example, we'll match names to the individual phone numbers sheet.
3. Scroll to the very end of the table and select the first cell, one row down (in this example Cell G2). Making sure that this cell is highlighted, type "=VLOOKUP(" into the formula bar (type an equal sign, the text VLOOKUP and an open parenthesis symbol), as pictured below:
.
4 . Now select the [lookup_value] by clicking on Cell A2. Type a comma into the formula after you have clicked Cell A2, and move on to the next step where will select the [table_array].
5. The [table_array] is the range of cells to which we are matching, so without clicking anywhere outside of the formula bar, click on the Individuals sheet to select the columns, or table of data, to compare. To select the [table-array], hover your mouse over the tops of the columns, so hover your mouse over "A," and you'll see a down arrow appear (highlighted below). Click and hold down your mouse, and select all the columns with the data you need (Hint: Columns A through F should contain all the name information, see below).
6. After you have highlighted these columns, type another comma in the formula bar, and we will now enter the [col_index_num], or the number of the column that you'd like displayed. For this example, type in the number "4" to display the content of the cells in Column D (or the individual's first name). [Note: to display the last name, move one column to the right and repeat these steps but type in the number "6" to display the contents of the cells in Column F, or the individual's last name.]
7. Type another comma into the formula bar, and now we will enter the [range_lookup]. This requires entering either the word TRUE or FALSE. TRUE will give an approximate match, and typing in FALSE will return an exact match. In this example, we want an exact match, so now type into the formula bar the word "FALSE."
8. Now we will finish the formula with a close parenthesis, so type into the formula bar ")" and hit enter. The first name should now display in the column for all the records on this page.
Your formula should appear similarly to the example below: