VLOOKUP across Multiple Sheets

Shawn Clemmons · 07/20/22

I am trying to have a cross reference that looks across 3 different sheets. I have been successful getting it to work with just two sheets, but when i add the third, it comes back "INCORRECT ARGUMENT SET". Any help is appreciated. Here is my formula

=IFERROR(VLOOKUP([Employee Email]@row, {Supervisors 1 Range 1}, 2, 0), VLOOKUP([Employee Email]@row, {Supervisors 2 Range 1}, 2, 0), VLOOKUP([Employee Email]@row, {Supervisors 3 Range 3}, 2, 0))

Paul Newcome · 07/20/22

Apologies. I missed that 3rd VLOOKUP in there. Yes. You need another IFERROR.

=IFERROR(IFERROR(VLOOKUP([Employee Email]@row, {Supervisors 1 Range 1}, 2, 0), VLOOKUP([Employee Email]@row, {Supervisors 2 Range 1}, 2, 0)), VLOOKUP([Employee Email]@row, {Supervisors 3 Range 3}, 2, 0))

Paul Newcome · 07/20/22

Yes. It would be the same syntax for the IFERROR portions. You would just replace VLOOKUP with INDEX/MATCH.

Paul Newcome · 07/20/22

WHat is the formula you are using that is throwing the error?

DW1 · 07/20/22

IFERROR only has two options; the initial formula to evaluate, and what to do if that initial formula results in an error. You are adding a 3rd choice here, which is incompatible with the IFERROR function.

You can use a combination of IF statements, ISERROR, and IFERROR to accomplish this.

IF(ISERROR(VLOOKUP([Employee Email]@row, {Supervisors 1 Range 1}, 2, 0)), IFERROR(VLOOKUP([Employee Email]@row, {Supervisors 2 Range 1}, 2, 0), VLOOKUP([Employee Email]@row, {Supervisors 3 Range 3}, 2, 0))

Shawn Clemmons · 07/20/22

WHat is the formula you are using that is throwing the error?<\/p>","bodyRaw":"[{\"insert\":\"WHat is the formula you are using that is throwing the error?\\n\"}]","format":"rich","dateInserted":"2022-07-20T18:50:14+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-09T23:38:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/336871#Comment_336871","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/336871#Comment_336871

@Paul NewcomeIt seems to be the last VLOOKUP that is in the formula I originally posted. I think it may have to do with adding additional IFERROR function, but haven't been able to resolve it.

Paul Newcome · 07/20/22

Apologies. I missed that 3rd VLOOKUP in there. Yes. You need another IFERROR.

=IFERROR(IFERROR(VLOOKUP([Employee Email]@row, {Supervisors 1 Range 1}, 2, 0), VLOOKUP([Employee Email]@row, {Supervisors 2 Range 1}, 2, 0)), VLOOKUP([Employee Email]@row, {Supervisors 3 Range 3}, 2, 0))

Shawn Clemmons · 07/20/22

@Paul NewcomeYou are a lifesaver! That worked. Due to the amount of cells allowed (100,000), I also had to remove some columns that contained additional data. But it worked!

@DW1I tried your method as well but it's giving me the 100,000 cell limit error message and won't display the values. Not sure why. I appreciate your solution and time as well.

Paul Newcome · 07/20/22

If you are running into the cells referenced limit, I suggest switching over to INDEX/MATCH. You only pull in the columns you are referencing instead of every column in between. You are also not restricted to which order the columns are in.

=INDEX({Column To Pull From}, MATCH(value_to_match_on, {Column To Match In}, 0))

Shawn Clemmons · 07/20/22

@Paul NewcomeCan INDEX/MATCH also be setup with the IFERROR function as well to achieve looking up values from the 3 sheets? I appreciate your help.

Paul Newcome · 07/20/22

Yes. It would be the same syntax for the IFERROR portions. You would just replace VLOOKUP with INDEX/MATCH.

Shawn Clemmons · 07/21/22

@Paul NewcomeI hate to keep bothering you but I can't seem to figure out the INDEX MATCH on my own. I am able to create the formula for looking at just one sheet and it works, but when I introduce the IFERROR and multiple sheets, it won't work. I'm sure it's some minor thing I am doing wrong, but I am stuck. Here is the formula I have tried:

=IFERROR(IFERROR(INDEX({Supervisor 1 ID}, MATCH([Email Address]@row, {Email Address}, 0)), INDEX({Supervisor 2 ID}, MATCH([Email Address]@row, {Email Address}, 0)), INDEX({Supervisor 3 ID}, MATCH([Email Address]@row, {Email Address}, 0)))

Paul Newcome · 07/22/22

I believe you may have some out of place parenthesis. Think of it this way... You want to wrap the ENTIRE first IFERROR in the second IFERROR.

First IFERROR:

=IFERROR(.................)

Now wrap that whole thing in the second

=IFERROR(IFERROR(.................), .....)

My suggestion is to write out the first INDEX/MATCH in its entirety. Then wrap that in the first IFERROR including the second INDEX/MATCH.

Now leave the cell. Just click out of it and go somewhere else for a minute.

When you come back to it, you want to add the next IFERROR to the beginning then go all the way to the end and add your comma, add in your INDEX/MATCH, then close it out.

bncole21 · 12/20/22

Good afternoon!

I attempted to use this template to create a multiple cross-sheet reference. It works for the first three sheets but everything after the first three makes the formula unparseable.

Any chance I can get some help? I have a handful more to add after this (a total of 36 sheets) but I wanted to try and get these first nine working first.

Thanks!

Formula:

=IFERROR(IFERROR(VLOOKUP([Service Order #]@row, {EIM1}, 2, 0), VLOOKUP([Service Order #]@row, {EIM2}, 2, 0)), VLOOKUP([Service Order #]@row, {EIM3}, 2, 0)), VLOOKUP([Service Order #]@row, {EIM4}, 2, 0), VLOOKUP([Service Order #]@row, {EIM5}, 2, 0), VLOOKUP([Service Order #]@row, {EIM6}, 2, 0), VLOOKUP([Service Order #]@row, {EIM7}, 2, 0), VLOOKUP([Service Order #]@row, {EIM8}, 2, 0), VLOOKUP([Service Order #]@row, {EIM9}, 2, 0)))

(As mentioned, the first three references work and then it dies :( )

Paul Newcome · 12/20/22

@bncole21You need to build in more IFERRORs

bncole21 · 12/20/22

@bncole21<\/a> You need to build in more IFERRORs<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"bncole21\",\"userID\":156159}}},{\"insert\":\" You need to build in more IFERRORs\\n\"}]","format":"rich","dateInserted":"2022-12-20T19:40:55+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-09T23:38:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/354999#Comment_354999","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/354999#Comment_354999

My goodness, that took some playing around, but I got it!! Thank you!!!

The woes of not being an excel master but using intermediate functions

Paul Newcome · 12/21/22

@bncole21Happy to help.️

VLOOKUP across Multiple Sheets

Best Answers

Answers

Help Article Resources

Categories