Master Sheet - Exceeds Row Limit

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

Master Sheet - Exceeds Row Limit

I am struggling to figure out how to have a "master" listing of customers that can be referenced by various sheets via INDEX/MATCH cross sheet formulas when that list is too large.

Here is my background:

I have a master customer listing of 6200 customers.  I use DataUploader to upload this into 3 sheets due to size limitations.  I have various "Roster" sheets that lookup  ~10 customers per sheet.  

My issues:

1. I do not have a unique identifier in the excel (upload) file, but DataUploader must delete and add new rows, therefore my formula and/or system created ID just gets deleted.

2. I want to use a lookup (INDEX/MATCH) on each Roster sheet so that we can type in the full name of a customer and have cells populate (address, contact info, etc.).  Even if I manually add my unique identifier formula into each sheet, I'm still unable to lookup against 3 separate sheets for each customer.

 

The only solution I am coming up with is to request a 1 sheet limit extension.  Am I missing something else that can work?

Thanks in advance!

Karen

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Because you are using INDEX/MATCH, you can leverage the MATCH in an IF statement. MATCH provides a numeric value, so if it returns anything above 0, then it is contained in that range. If we use this thought process, we can use a nested IF statement to determine which INDEX/MATCH should be run.

     

    =IF(MATCH([ID Column]@row, {1st Sheet ID Range}, 0) > 0, INDEX({1st Sheet Data Range}, MATCH([ID Column]@row, {1st Sheet ID Range}, 0)), IF(MATCH([ID Column]@row, {2nd Sheet ID Range}, 0) > 0, INDEX({2nd Sheet Data Range}, MATCH([ID Column]@row, {2nd Sheet ID Range}, 0)), IF(MATCH([ID Column]@row, {3rd Sheet ID Range}, 0) > 0, INDEX({3rd Sheet Data Range}, MATCH([ID Column]@row, {3rd Sheet ID Range}, 0)))))

  • L@123[email protected] ✭✭✭✭✭
    edited 08/30/19

    you could simplify this using concatenation

    =iferror(INDEX({1st Sheet Data Range}, MATCH([ID Column]@row, {1st Sheet ID Range}, 0)),"")+

    iferror(INDEX({2nd Sheet Data Range}, MATCH([ID Column]@row, {2nd Sheet ID Range}, 0)),"")+

    iferror(INDEX({3rd Sheet Data Range}, MATCH([ID Column]@row, {3rd Sheet ID Range}, 0)),"")

  • Thanks Paul and [email protected]!  I'll try these out today.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I like it, but...

     

    Since #NO MATCH triggers the IFERROR, why not take it one step further and nest each one into the previous statement?

     

    =iferror(INDEX({1st Sheet Data Range}, MATCH([ID Column]@row, {1st Sheet ID Range}, 0)), iferror(INDEX({2nd Sheet Data Range}, MATCH([ID Column]@row, {2nd Sheet ID Range}, 0)), iferror(INDEX({3rd Sheet Data Range}, MATCH([ID Column]@row, {3rd Sheet ID Range}, 0)),"")))

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

     

    Let us know how it goes.

  • L@123[email protected] ✭✭✭✭✭

    I didn't know if there could be multiple matches, and my way would concatenate them instead of stopping at the first match. If the list is unique then this way would be better.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Very true. I hadn't thought about matches on more than one sheet in case of overlap during the upload. In that case I personally would replace "" in your formula with some kind of delimiter such as "/". Then I could use conditional formatting to bring any cells containing a "/" to my attention so I can look into which data should and should not be displayed.

Sign In or Register to comment.