Nested VLOOKUPs for Salesforce Admins

I recently needed to check if an email address existed in one of the three Contact Email fields and return the Record ID. How did I do it? I used Excel Nested VLOOKUPS, also called Chained VLOOKUPs in order to find/compare the values and return the Record ID that I needed.

Scenario
As you can see in the image, I had four columns with email addresses.

1st Column: The List of email addresses that needed to be looked for. (The source of this list was an External Platform that we use for Marketing Purposes)
2nd Column: Email
3rd Column: Email 2
4rd Column: Email 3

Generic Formula
=IFERROR(VLOOKUP 1, IFERROR(VLOOKUP 2, VLOOKUP 3))

My Formula
=IFERROR(VLOOKUP(A2,$E$2:$H$4,4,FALSE),IFERROR(VLOOKUP(A2,$F$2:$H$4,3,FALSE),VLOOKUP(A2,$G$2:$H$4,2,FALSE)))

Explanation
The reason why I used nested VLOOKUPS is because we need to sweep column by column and return the record ID if the value is found.

Have in mind
VLOOKUP
returns the #N/A error when it fails to find the lookup value in the given table. IFERROR returns an specified value if #N/A error was returned.

This is how my formula works
The first VLOOKUP runs. If it finds the value, it returns the Record ID, if it fails, it returns #N/A error and checks in the next Column. If it finds the value, it returns the Record ID, if it fails, it returns #N/A error and checks in the next Column. If it finds the value, it returns the Record ID, if it fails, it returns #N/A error.
At the end I got the Record ID that I needed to perform my update.

I hope you found this helpful. I know, managing data can drain your time but sometimes you’ve gotta do what you’ve gotta do.

Have a nice week!

Do you need to “map” a Lead Custom Field to a Standard Contact Field during conversion?

Not possible! in Salesforce, by concept you can only map Standard Fields to Standard Fields and Custom Fields to Custom Fields but what’s your way around? If you run into this requirement this is how you could do it.

Create a Process that Updates a Field when Converting a Lead into a Contact in Process Builder. Something like this:

Object*: Lead
Start the process*: when a record is created or edited

Node: Name you Node
Criteria for Executing Actions*: Conditions are met
Set Conditions: [Lead]. IsConverted Equals Boolean True
Conditions: All of the conditions are met (AND)

Immediate Actions
Action Name: Name your Action
Record*: [Lead].Converted Contact ID
Criteria for Updating Records*: No criteria—just update the records!

Set new field values for the records you update
Mobile Phone Field Reference [Lead].Home_Phone__cc

Very straightforward. The only tricky part is knowing which field value to update when setting new field values for the records you update. Process Builder shows the Field Labels and not the API field names which can be a little confusing but I recommend checking the SOAP API Developer Guide for those purposes.