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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s