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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • DW1
    DW1 ✭✭✭✭
    edited 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))

  • @Paul Newcome It 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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome You 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!

    @DW1 I 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
    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))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome Can 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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome I 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
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • bncole21
    bncole21 ✭✭✭

    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
    Paul Newcome ✭✭✭✭✭✭

    @bncole21 You need to build in more IFERRORs

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • bncole21
    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 ✭✭✭✭✭✭

    @bncole21 Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!