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.
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
=IFERROR(VLOOKUP 1, IFERROR(VLOOKUP 2, VLOOKUP 3))
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!