INDEX & MATCH Columns

Options

Hi, I tried to Index & Match 15 columns in a New sheet with the existing Master sheet. However, it only allowed 10 columns only - attached below message. Please advise.

Thank you.


Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @AllisonFoo

    I hope you're well and safe!

    How many rows are there in the sheets?

    Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • AllisonFoo
    Options

    Hi Andree,

    In the Master sheet, there is 8582 roles. I would like to Index & Match 15 columns in the New sheet. However, I can only INDEX and MATCH 10 columns in the new sheet.

    Thank you.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @AllisonFoo The limit of 100,000 cross-sheet links on a sheet can be calculated by number of rows referenced by the range in each INDEX/MATCH multiplied by the number of columns using INDEX/MATCH. So, 8582 rows in the lookup sheet would allow a maximum of 11 columns in the new sheet to reference those 8582 rows. 11 x 8582 = 94,402.

    To work around this, I have used multiple sheets to look up values from large sheets, and then used direct cell-links between those sheets to bring all the values into one sheet. So if I have 7 Index/Match column in Sheet 1, and 8 Index/Match columns in Sheet 2, I can use direct cell link on Sheet 1 to bring into the values from Sheet 2.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @AllisonFoo

    Happy to help!

    To add to Jeff's excellent advice/answer.

    • When I run into this problem in my client solutions, I usually join values together in the Source and then Split them up in the Destination sheet.

    Make sense?

    Would that work/help?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • AllisonFoo
    Options

    Thanks Jeff & Andree :)

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @AllisonFoo

    Excellent!

    You're more than welcome!

    Remember! Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!