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

Find more posts tagged with

Formulas

Project Management

Importing and Exporting

Cell linking

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…