7

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

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

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

In reply to by [email protected]

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

In reply to by [email protected]

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.