INDEX & MATCH Columns

AllisonFoo

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.

Andrée Starå

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!

AllisonFoo

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

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

Andrée Starå

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

AllisonFoo

Thanks Jeff & Andree :)

Andrée Starå

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