VLOOKUP(), new to me
Some days it seems like I live in Excel. I do a ton of data manipulation in my CRM projects. I use Access a lot, because I can churn out queries to relate data together for Inserting or Updating in Salesforce.
Excel and Access formulas are some of my best tools for hacking through the tall grass of customer data and getting to the root of it. Funny, then, that a powerful formula like VLOOKUP() escaped me until yesterday.
VLOOKUP() lets you do a join between two data sets that are in Excel. It doesn’t have the power of Access queries, but if all you want is the Salesforce Contact Id, this can make it really quick.
From the MS Help:
VLOOKUP searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table.
That explains it, no? Nice help file. I’ll try to describe the process in English.
- We had a sheet of Donations for Contacts, but only had the Legacy ID for the Contacts
- We had already insterted all the Contacts to Salesforce.com with their Legacy Ids
- We pulled them out with the data loader, just Contact ID and Legacy ID
- We created a new sheet and put the data in there, making sure the furthest left column was Legacy Id
- We named the data range “Contacts”
- In the donations sheet, we created a column for SF Contact Id, and put this formula in there =VLOOKUP(B2,Contacts,2), where B2 is the Legacy Id on the donations sheet, and the 2 tells Excel to get the data in the second column of the Contacts range.
- The Contact Ids are then shown in that Column and can be used to create Contact Roles for the donations.
Joins without a database!
May 1st, 2008 at 11:41 am
I would suggest anyone and everyone how has every worked with Excel know this one function! That and the use of the “If” function. I’m surprised at those people who live and die in excel don’t know these functions. I guess since I maybe a little younger and had part of my education as working with/in excel I take it for granted.
Even though the ‘help’ doesn’t explain that much I use it all the time just to see the examples.
May 1st, 2008 at 12:05 pm
My other favorite functions for dealing with data: If(), IsBlank(), Not(), And(), Or(). Find() is great but annoying because if it doesn’t find the text in the field it throws an error, rather than returning null or something useful.
May 1st, 2008 at 1:06 pm
Agreed - vlookup has been a lifesaver on several occasions.
But a word of caution when using with the vlookup function with SFDC data - vlookup is NOT case sensitive, so if you are looking up SFDC data imported into Excel via a report/office plugin and therefore the 15 char Ids, it may bring back incorrect matches.
You can do an exact match with an amended vlookup (see http://support.microsoft.com/kb/214264), or you can create a custom auto-number field as an External Id in SFDC and use that for lookups.
Re Steve’s point, iserror() is another great function to wrap vlookup in - if it doesn’t find a match, you can then return something like a 0, rather the #N/A which screws up numeric functions.
May 1st, 2008 at 1:50 pm
Awesome, I’ll try out IsError()! Gotta love the 15 to 18 character case-sensitiveness of Salesforce Ids!
May 1st, 2008 at 3:48 pm
Very nice. Also can be useful combined with the excel connector.
May 1st, 2008 at 5:20 pm
Kudos for posting this! In my experience, you need to watch how you use the final argument in the formula, the Range_lookup. I almost always set it to “FALSE”. From MS Help:
Range_lookup specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.
Pivot Table is the other secret, power user feature in Excel. Use it with VLOOKUP and you’re en fuego! You can even do custom formulas and fields in the Pivot Table. I also use Pivot Tables to query Access databases, when my record set exceeds 65K rows (Excel 2003’s limit).
Recently, I was interviewing for an intern position. I asked the candidates what their power user function was. A few people knew PivotTables, but the guy who really impressed me said “Arrays”. I still don’t know what it does or how to use it….
May 1st, 2008 at 6:21 pm
VLOOKUP is good times….but you could also do this with DemandTools and the Find/Report IDs module. Super handy.
May 1st, 2008 at 8:29 pm
VLOOKUP is a must. In fact, Salesforce even started using it in their formulas. It’s only available on Validation Rules, but it works ina similar way.
I used VLOOKUP extensively very recently when I moved myself from a powered up DE org to a new EE org. As I made the migration, I would use an Excel sheet with 2 columns in it (Old ID & New ID). Then as I loaded in data, I would use VLOOKUP to make sure my associations were right. For example, say I was working on a Contacts load. I would have already completed the Account load. In order to populate the Account ID on Contact, I’d do a VLOOKUP in my Id mapping table to translate the old value to new. Voila! This simple approach allowed me to move my data very quickly using the Excel connector.
One very important thing: there is a final parameter in the formula that is either TRUE or FALSE. In almost every circumstance you want to put FALSE. This forces an exact match. If I exclude this parameter, I have had occassions where the VLOOKUP picks the wrong row. I can’t explain why an Exact Match isn’t the default, but it’s not. Make sure you use that last parameter.
May 2nd, 2008 at 8:36 am
The point on case sensitive Id’s is a good one. Another fix for this issue is to install the sforce excel connector. This comes with a “Fixid()” function which automatically converts 15 character Id’s (not case sensitive) to 18 character Id’s (which are case sensitive).
May 6th, 2008 at 10:09 pm
After performing a VLOOKUP, I usually do a COPY - PASTE (SPECIAL) AS TEXT. Then I sort the sheet by that column and find all the rows that returned an error. I can then do a search-replace to clear those cells and can load the rows with values in that column.
Very important to do the paste(special), as every time you resort the sheet, Excel will recalculate the vlookup. For lots of rows in the source and destination sheet, this can take a very long time.
That said, VLOOKUP is good for a one-off, but for implementation data-loads, it is far better to load everything into Access, as it’s faster and can make better queries. Ezra Kenigsberg gave a super talk at Dreamforce 2007 about how to do this: http://blogs.salesforce.com/dreamforce07/2007/10/applied-data-qu.html
May 11th, 2008 at 7:35 pm
I love VLOOKUP, don’t know how I do anything in Excel without it.
I blogged about a workaround to the case sensitivity issue on my site here. It works.