CODEWITHSUNDEEP
excel
Brent Adams
Brent Adams

Reputation: 11

Conditional Index/Match trouble

table1

table2

Hello, I am trying to solve an issue that has my brain fried. I have two tables, Table1 and Table2. Table1 has a list of SKUs as well as a department/class/fine which are essentially a breakdown of product groups. There is a hierarchy here. Each department has a sub-CLASS and each class can be broken up into smaller fine lines. Department > class > Fine line. Table2 contains markup information. I am trying to match the correct markup in table 2 with the corresponding DCF in table1. Here's the kicker: Not all markups correspond to an exact DCF, some markups just apply to the specific department/class or even just the department.

Here's my logic:

  1. IF DCF(Dept / class/ fine) from table1 match table2, find the cus1 markup record with the matching DCF and return it to table1.
  2. If Fine is missing, return the value for matching Dept TRUE/ Class True / Fine NULL.
  3. If both Class and Fine are missing, return the value of just the department TRUE / Class NULL / Fine NULL.

I can get this equation to work every time if all 3 DCF match but when I start throwing logical tests into the mix, I cannot seem to find a way to get excel to find the correct cell to index. Here is my current iteration of this formula (I am missing the final criteria here. I only have steps 1 and 2 from my logic in this equation, will need to add the final criteria once I get it ironed out).

Here is my formula:

=IF(AND(
       COUNTIFS(Table2[dept],[@dept],Table2[class],[@class])>0,
       COUNTIFS(Table2[fine],[@fine])<1),
  INDEX(Table2[markup],MATCH(Table1[[#Headers],[cus1]]&[@dept]&[@class],Table2[custype]&Table2[dept]&Table2[class],0)),
  INDEX(Table2[markup],MATCH(Table1[[#Headers],[cus1]]&[@dept]&[@class]&[@fine],Table2[custype]&Table2[dept]&Table2[class]&Table2[fine],0)))

Formula breakdown:

If table2[dept] matches [@dept] and table2[class] matches [@class] and table2[fine] != [@fine] then match the values in table2 and return the markup index. The issue with this formula is that it returns the first instance of department/class and ignores whether or not the class is missing or not. Line 3 on Table1 is returning 0.65 and should be returning 0.4.

Upvotes: 1

Views: 47

Answers (1)

basic
basic

Reputation: 11968

You can try following formula:

=INDEX(M:M,
   AGGREGATE(15,6,
      ROW(Table2[markup])/
      (((([@dept]=Table2[dept])*4+(([@class]=Table2[class])*3)+(([@fine]=Table2[fine])*2+(""=Table2[class])+(""=Table2[fine]))))=
      MAX(((([@dept]=Table2[dept])*4+(([@class]=Table2[class])*3)+(([@fine]=Table2[fine])*2+(""=Table2[class])+(""=Table2[fine])))))),1))

enter image description here

Upvotes: 1

Related Questions