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

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])