INDEX & MATCH Columns
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
-
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.
-
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.
-
@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!
-
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.
-
Thanks Jeff & Andree :)
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!