VLOOKUP across Multiple Sheets
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))
Best Answers
-
Paul Newcome ✭✭✭✭✭✭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 ✭✭✭✭✭✭
Yes. It would be the same syntax for the IFERROR portions. You would just replace VLOOKUP with INDEX/MATCH.
Answers
-
Paul Newcome ✭✭✭✭✭✭
WHat is the formula you are using that is throwing the error?
-
DW1 ✭✭✭✭
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))
-
@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 ✭✭✭✭✭✭
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 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 ✭✭✭✭✭✭
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))
-
@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 ✭✭✭✭✭✭
Yes. It would be the same syntax for the IFERROR portions. You would just replace VLOOKUP with INDEX/MATCH.
-
@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 ✭✭✭✭✭✭
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 ✭✭✭Good afternoon!
我试图用这个模板来创建一个多ple 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 ✭✭✭✭✭✭
-
bncole21 ✭✭✭
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 ✭✭✭✭✭✭
-
@Paul Newcome=IFERROR(IFERROR(VLOOKUP([RFID #]@row, {Traxx - Lab Asset data (20,000)}, 2, 0), VLOOKUP([RFID #]@row, {Traxx - Lab Asset data (40,000)}, 2, 0), VLOOKUP([RFID #]@row, {Traxx - Lab Asset data (60,000)}, 2, 0), VLOOKUP([RFID #]@row, {Traxx - Lab Asset data (80,000)}, 2, 0), [RFID #]@row, {Traxx - Lab Asset data (100,000)}, 2, 0), VLOOKUP([RFID #]@row, {Traxx - Lab Asset data (120,000)}, 2, 0), VLOOKUP([RFID #]@row, {Traxx - Lab Asset data (140,000)}, 2, 0), VLOOKUP([RFID #]@row, {Traxx - Lab Asset data (160,000)}, 2, 0), VLOOKUP([RFID #]@row, {Traxx - Lab Asset data (180,000)}, 2, 0))
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
