VLOOKUP over multiple sheet

jmo

I have a new sheet that has Full name and I want to do a VLOOKUP to determine their email address w/o asking them for it.

I need to look across 4 massive sheets (almost 20k rows in each) and when the Full Name is X it returns their email address.

I know how to do this if in 1 sheet but not across 4 sheets.

Sheet names are:

• Employee list 1
• Employee list 2
• Employee list 3
• Employee list 4

Columns on each of those sheets are the same:

• EID
• Full name
• Email

Any best practices for getting at this?

Thanks.

Paul Newcome

I suggest using a series of INDEX/MATCH formulas nested in IFERROR functions.

=INDEX({Sheet 1 Email}, MATCH([Full Name]@row, {Sheet 1 Full Name}, 0))

Then we get an error if it is not is sheet 1, so we use an IFERROR function to tell it to run the first, if there is an error with that, then run the second.

=IFERROR(INDEX({Sheet 1 Email}, MATCH([Full Name]@row, {Sheet 1 Full Name}, 0)), INDEX({Sheet 2 Email}, MATCH([Full Name]@row, {Sheet 2 Full Name}, 0)))

Then we throw on another IFERROR to say that if that causes an error, do the INDEX/MATCH on sheet 3.

=IFERROR(IFERROR(INDEX({Sheet 1 Email}, MATCH([Full Name]@row, {Sheet 1 Full Name}, 0)), INDEX({Sheet 2 Email}, MATCH([Full Name]@row, {Sheet 2 Full Name}, 0))), INDEX({Sheet 3 Email}, MATCH([Full Name]@row, {Sheet 3 Full Name}, 0)))

Then finally we use yet another IFERROR to say that if there is still an error, run it on Sheet 4.

=IFERROR(IFERROR(IFERROR(INDEX({Sheet 1 Email}, MATCH([Full Name]@row, {Sheet 1 Full Name}, 0)), INDEX({Sheet 2 Email}, MATCH([Full Name]@row, {Sheet 2 Full Name}, 0))), INDEX({Sheet 3 Email}, MATCH([Full Name]@row, {Sheet 3 Full Name}, 0))), INDEX({Sheet 4 Email}, MATCH([Full Name]@row, {Sheet 4 Full Name}, 0)))

Heather Duff

Thank you, @Paul Newcome ! I was trying to figure out if you could nest IFERRORs.

Paul Newcome

@Heather D Why yes you most certainly can. Haha.

jmo

Well @Paul Newcome , looks like I'm running into a cross-sheet formula limitation and I didn't even get the full formula built out:

^^^ That popped up after I did =IFERROR(IFERROR(INDEX({Sheet 1 Email}, MATCH([Full Name]@row, {Sheet 1 Full Name}, 0)), INDEX({Sheet 2 Email}, MATCH([Full Name]@row, {Sheet 2 Full Name}, 0))), INDEX({Sheet 3 Email}, MATCH([Full Name]@row, {Sheet 3 Full Name}, 0)))

and the cell just gets stuck at CALCULATING.

Any ideas?

More background - the 4 sheets I have to cross reference were created because of the Smartsheet 20k row limitation. Our employee list is over 79k rows.

Paul Newcome

How large is the working sheet (the one we are putting the formula in)? We are going to have to figure out a way to break this down into multiple parts.

jmo

@Paul Newcome - the sheet that has or is receiving the formula is super small. I just set it up as a test case with 5 rows & 3 column with one of them needing to utilize the formula for email lookup.

The Full name and Sheet name pulled from are just text fields.

Paul Newcome

OK. What about the sheet you are going to actually build? How large do you anticipate the sheet becoming?

jmo

What I am hoping to do is leverage this vlookup for anyone that inputs their full name on any of my future sheets to have their email automatically populated in an Email/contact list column. If their email doesn't come back in vlookup then a notification is sent to the person requesting a manual update to the sheet.

For for this use case I'll have various sheets with all of them having a "user" field for their full name and I want the email to automatically populate. These sheets can have anywhere from 10 - 100 rows. With various counts of columns (not just the 3 mentioned above).

Paul Newcome

In that case what you are wanting to do isn't exactly possible unfortunately. The only way to accomplish this would be to break each working sheet down into sections. Basically you would have a working sheet and 4 reference sheet that duplicate the working sheet. You would have one duplicate for each listing sheet then have those results tie back into the main working sheet.

It is a work-around that would require some setup but then be relatively manageable if you were only using this to pull for one main sheet, but from the sound of this, you want to be able to use this multi-sheet database for ALL of your sheets which unfortunately is going to require quite a bit of work every time a new sheet is created.

jmo

Yeah, that's a real bummer @Paul Newcome. I can work without this but was just trying to minimize the number of times I ask someone for their email address.

Appreciate your responses.

Paul Newcome

What are the chances their names are built into the email addresses (internal use) or is it more variable?

jmo

I'm not sure I follow what "names built into the email addresses" means.

Can you elaborate?

Paul Newcome

So that would be something like standardized company emails such as "[email protected]" or "[email protected]" or even "[email protected]". If every single email address is formatted the same way based off of the name, we may be able to use a formula to pull the text from he name field to generate the appropriate email address instead of having to hit against a listing.

jmo

Ah yes - for the most part, emails in our company are [email protected]

There are exceptions, like mine, where my name is Jeffrey but I go by Jeff so my primary email is j[email protected] and my alias is [email protected].

But we don't have to solve for the exceptions right now.

Paul Newcome

As long as people type in the name that is listed in their email address with a single space between first and last name and do not include any additional characters such as titles or suffixes...

=SUBSTITUTE([Full Name]@row, " ", ".") + "@company.com"

Basically we use the SUBSTITUTE function to swap out the space for a "dot" and then tack the "@company.com" onto the end thus replicating the email address. As long as this is consistent and placed in a contact type column, it can be leveraged to Automations as well.