Master Sheet - Exceeds Row Limit

kgb2019

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

Paul 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

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

kgb2019

Thanks Paul and L@123!  I'll try these out today.

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

Happy to help! 

 

Let us know how it goes.

L_123

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