IF INDEX MATCH Combination - Matching part of a string & More than one column:)

First post, hope this is ok.

I currently have 2 workbooks.

Workbook 1 is a master products sheet that contains over 50,000 product SKU’s/ISBNS (we sell books. These ISBNs are mostly 13 digits long and numerical. They all start with the first 3 digits ‘978’.
Workbook 2 is a master discount sheet that contains a list of all available Publishers for the books & the discount we receive from those publishers.

A large number of the publishers/imprints in workbook 2 are further broken down into ISBN prefix brackets for which we receive differing levels of discount. See tables below attached:

Workbook 1 = Orange
Workbook 2 = Blue

What we’d really appreciate help with, is looking up the discount received in workbook 2 and attributing it to products in workbook 1 using the combined Publisher and Prefixes.
Something along the lines of:

If the Publisher name for the product = (Bob's Publishers) & the ISBN contains the digits (9351130) after the first 3 numbers, return the value in the discount received cell.

I’ve tried a mixture of things today but had no luck at all. I assume it’s a mixture of nested functions utilizing INDEX MATCH and IF tests but I’ve failed miserably so far. Any help would be very much appreciated😊

Thank you guys,

workbooks 1 & 2 combined in 1 image.workbooks1and2.JPG

posting files is always better than an image, however - some questions:

1 - you say workbooks, do you mean workbooks (different files) or worksheets (same file) ?
If answer is workbooks, is the discount file always open when calculating "Products sheet" ?
2 - you don't indicate expected results and given fact your discount table is not mutually exclusive it's not clear which is the correct discount for Jim's publishers
presumably 14198 takes precedent over 141 - will your look up table always order the "keys" in this way - i.e. most detailed match to least detailed

I have to head off but in case you need something to work from below is one possible route:

Formula:

Please Login or Register  to view this content.

modify ranges to suit but keep the ranges on Discount Sheet to a minimum.

note: above assumes, per earlier post, that you list the discount keys in order of most detailed to least detailed per Publisher - e.g. 14198 to 141

Hi Xlent, thank you so much for replying I REALLY appreciate it (still struggling with it as we speak).

I’ve now been able to get more information including a column of unique Publisher Codes from our ERP system that may well help significantly with this task.
In response to your questions:

RE: posting files is always better than an image

Sorry about that. I’ve uploaded a file of data that should make the problem a bit easier to solve (with some annotations too. Although (as below) we will ultimately be using 2 separate workbooks I’ve put both discounts and products information in the same workbook to make life a bit easier hopefully when experimenting😊

RE: 1 - you say workbooks, do you mean workbooks (different files) or worksheets (same file)? If answer is workbooks, is the discount file always open when calculating "Products sheet"?

Yup workbooks, we want the managing director of the company to be able to update/change discounts received from publishers in the discount sheet and for those updated values to also update in the products sheet. Usually I just make sure I save whenever content is changed in the discount sheet and click enable content each time I open the products sheet thereafter. Will this keep everything in sync ok?


RE: 2 - you don't indicate expected results and given fact your discount table is not mutually exclusive it's not clear which is the correct discount for Jim's publishers
presumably 14198 takes precedent over 141 - will your look up table always order the "keys" in this way - i.e. most detailed match to least detailed

Apologies for not explaining this one so well. I’m hoping the addition of the ‘Publisher Code’ column (which has all unique values) will help a bit.

So basically, we have different levels of discount available for different ISBN prefixes that fall under the same publisher (annoyingly). So, we want to show those differing levels of discount depending on the publisher and that specific prefix.
To make things more complex (as you can see in the workbook) we also have instances of the same Publisher, & Prefix but different distributors which again affects the discount received. To top it all off we even have records that are almost identical but have different publisher codes see B7 & B8 on the Discount sheet.

Hopefully the new publisher code column with its unique values will help with this mess and perhaps mean that we don’t need to list the isbn prefixes in order of most detailed to least detailed per publisher, and can instead hook into those unique values?


Thank you so much for your help.

Best

James

Hi, first off, looking at the sample file --

could you outline how you can determine the distributor from the info provided on the Product sheet - e.g. row 16?
I can't see any obvious means of differentiating Ingram International from Faber Music - which would be required to isolate the discrete discounts.

As far as I can infer the above is the only o/s issue in terms of retrieving the correct discounts?
You mention an issue with leading zeroes on Discount sheet but that shouldn't cause an issue if those cells are stored as text rather than number.

In terms of removing the 0s from the results, you can do this using an IFERROR approach, and to avoid double evaluation of the potentially expensive calc you can get away with:

Formula:

Please Login or Register  to view this content.

the 1/(1/x) will generate #DIV/0! if x = 0 else x ... and you can wrap around an error handler to convert the #DIV/0! to your preferred null string ("").

Hi mate,

So since I posted last we've had some good news. We can attribute the majority of titles (40k+) with their relevant publication codes which are all unique.

This means that a straight forward index match (see purple on product sheet) seems to do the business as we're only referencing unique values every time. Does this seem accurate to you?



I'm afraid I've not been able to implement the: =IFERROR(1/(1/{existing formula}),"") fix you kindly gave us. Would you mind placing that around a working piece of formula from the sheet if possible?

Its a good thing that I've been able to get these publication codes as it solves a real headache but in some ways it would've been good to get to the bottom of the initial, more complex, question to improve my limited knowledge!!


Hopefully this'll work

best

James

So, re: removing 0 returns:

Formula:

Please Login or Register  to view this content.

you could, alternatively, apply a custom number format to "hide" 0, if preferred but the above will, of course, handle other "errors" also unlike a format.

the INDEX/MATCH route is the way to go if, in reality, the Discounts are to be held in a different file
if the Discounts were in the same file (like the sample) I'd advocate SUMIF over INDEX/MATCH - but former requires "open" references.

Amazing. Thank you so much for your help with this. I will test this properly on the full part catalogue tomorrow and adjust the thread accordingly.


Have a good evening my friend!