Tough Formula

[Biz]

Dear All,

I have a very hard problem to solve in Excel 2010 at work.
I can't change "Table" worksheet tab.
In "Answers" worksheet tab, I'm trying to construct a non array formula In Col D and Should Be Col E.



Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​
1​	Agreement Type	Trigger 1	T1	Trigger 2	T2	Trigger3	T3
2​	Cr1	1​	3​	20,000​	6​	60,000​	12​
3​	Cr2	20,000​	3​	25,000​	6​	60,000​	12​
4​	Cr3	20,000​	3​	25,000​	6​	60,000​	12​
5​	Cr4	20,000​	3​	25,000​	6​	60,000​	12​
6​	Cr5	20,000​	3​	25,000​	6​	60,000​	12​
7​	Cr6	20,000​	3​	25,000​	6​	60,000​	12​
8​	Cr7	20,000​	3​	25,000​	6​	60,000​	12​
9​	Cr8	20,000​	3​	25,000​	6​	60,000​	12​
10​	Cr9	20,000​	3​	25,000​	6​	60,000​	12​
11​	Cr10	20,000​	3​	25,000​	6​	60,000​	12​
12​	Cr11	20,000​	3​	25,000​	6​	60,000​	12​
13​	Cr12	20,000​	3​	25,000​	6​	60,000​	12​
14​	Cr13	21,000​	3​	25,000​	6​	65,000​	12​
15​	Cr14	20,000​	3​	25,000​	6​	60,000​	12​
16​	Cr15	20,000​	3​	25,000​	6​	60,000​	12​
17​	Cr16	20,000​	3​	30,000​	6​	70,000​	12​

Sheet: Table

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​					Should Be
2​	Cr1	19,000​		=INDEX(Table!$B$2:$G$17,MATCH($A2,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B2:$G2<=$B2)*(Table!$B2:$G2),1),Table!$B2:$G2,0)+1)​	3​
3​	Cr2	25,000​		=INDEX(Table!$B$2:$G$17,MATCH($A3,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B3:$G3<=$B3)*(Table!$B3:$G3),1),Table!$B3:$G3,0)+1)​	6​
4​	Cr3	19,000​		=INDEX(Table!$B$2:$G$17,MATCH($A4,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B4:$G4<=$B4)*(Table!$B4:$G4),1),Table!$B4:$G4,0)+1)​	3​
5​	Cr4	26,000​		=INDEX(Table!$B$2:$G$17,MATCH($A5,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B5:$G5<=$B5)*(Table!$B5:$G5),1),Table!$B5:$G5,0)+1)​	6​
6​	Cr5	100,000​		=INDEX(Table!$B$2:$G$17,MATCH($A6,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B6:$G6<=$B6)*(Table!$B6:$G6),1),Table!$B6:$G6,0)+1)​	12​
7​	Cr6	4,000​		=INDEX(Table!$B$2:$G$17,MATCH($A7,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B7:$G7<=$B7)*(Table!$B7:$G7),1),Table!$B7:$G7,0)+1)​	3​
8​	Cr7	24,000​		=INDEX(Table!$B$2:$G$17,MATCH($A8,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B8:$G8<=$B8)*(Table!$B8:$G8),1),Table!$B8:$G8,0)+1)​	3​
9​	Cr8	25,000​		=INDEX(Table!$B$2:$G$17,MATCH($A9,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B9:$G9<=$B9)*(Table!$B9:$G9),1),Table!$B9:$G9,0)+1)​	6​
10​	Cr9	19,000​		=INDEX(Table!$B$2:$G$17,MATCH($A10,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B10:$G10<=$B10)*(Table!$B10:$G10),1),Table!$B10:$G10,0)+1)​	3​
11​	Cr10	26,000​		=INDEX(Table!$B$2:$G$17,MATCH($A11,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B11:$G11<=$B11)*(Table!$B11:$G11),1),Table!$B11:$G11,0)+1)​	6​
12​	Cr11	100,000​		=INDEX(Table!$B$2:$G$17,MATCH($A12,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B12:$G12<=$B12)*(Table!$B12:$G12),1),Table!$B12:$G12,0)+1)​	12​
13​	Cr12	4,000​		=INDEX(Table!$B$2:$G$17,MATCH($A13,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B13:$G13<=$B13)*(Table!$B13:$G13),1),Table!$B13:$G13,0)+1)​	3​
14​	Cr13	65,000​		=INDEX(Table!$B$2:$G$17,MATCH($A14,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B14:$G14<=$B14)*(Table!$B14:$G14),1),Table!$B14:$G14,0)+1)​	12​
15​	Cr14	60,000​		=INDEX(Table!$B$2:$G$17,MATCH($A15,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B15:$G15<=$B15)*(Table!$B15:$G15),1),Table!$B15:$G15,0)+1)​	12​
16​	Cr15	60,000​		=INDEX(Table!$B$2:$G$17,MATCH($A16,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B16:$G16<=$B16)*(Table!$B16:$G16),1),Table!$B16:$G16,0)+1)​	12​
17​	Cr16	60,000​		=INDEX(Table!$B$2:$G$17,MATCH($A17,Table!$A$2:$A$17,0),MATCH(AGGREGATE(14,6,(LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B17:$G17<=$B17)*(Table!$B17:$G17),1),Table!$B17:$G17,0)+1)​	12​

Sheet: Answer

Your help would be greatly appreciated. I'm thinking Index & Match with aggregate may help.

Kind Regards

Biz

 

[Peter_SSs]

Edit 1: Since posting I note that you have mentioned your version in post 1 but the following would be a better place as it is then always available.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Edit 2: Even though I am now aware that you do not have Excel 365 I will leave this suggestion in case it is of use to others.

For example, IF you have Excel 365 and the LET function, then this shorter formula produces the same results as column C in post #7.

Biz.xlsm
	A	B	C
1	Agreement Type	Amt	Result
2	Cr1	19,000	3
3	Cr2	25,000	6
4	Cr3	19,000	0
5	Cr4	26,000	6
6	Cr5	100,000	12
7	Cr6	4,000	0
8	Cr7	24,000	3
9	Cr8	25,000	6
10	Cr9	19,000	0
11	Cr10	26,000	6
12	Cr11	100,000	12
13	Cr12	4,000	0
14	Cr13	65,000	12
15	Cr14	60,000	12
16	Cr15	60,000	12
17	Cr16	60,000	6
Answer

Cell Formulas
Range	Formula
C2:C17	C2	=LET(rw,FILTER(Table!B$2:G$17,Table!A$2:A$17=A2),hdr,Table!B$1:G$1,IFNA(INDEX(FILTER(rw,LEN(hdr)=2),MATCH(B2,FILTER(rw,LEN(hdr)>2))),0))

 

[Peter_SSs]

So here is another formula of similar length that is applicable to excel 2010 and higher.

Biz.xlsm
	A	B	C
1	Agreement Type	Amt	Result
2	Cr1	19,000	3
3	Cr2	25,000	6
4	Cr3	19,000	0
5	Cr4	26,000	6
6	Cr5	100,000	12
7	Cr6	4,000	0
8	Cr7	24,000	3
9	Cr8	25,000	6
10	Cr9	19,000	0
11	Cr10	26,000	6
12	Cr11	100,000	12
13	Cr12	4,000	0
14	Cr13	65,000	12
15	Cr14	60,000	12
16	Cr15	60,000	12
17	Cr16	60,000	6
Answer

Cell Formulas
Range	Formula
C2:C17	C2	=IFERROR(AGGREGATE(14,6,Table!C$2:G$17/((Table!A$2:A$17=A2)*(LEN(Table!C$1:G$1)=2)*(Table!B$2:F$17<=B2)*(LEN(Table!B$1:F$1)>2)),1),0)

 

[Biz]

As you sift through the formula, the following might be helpful:

The formula can be split apart into several components. The [main formula] is wrapped in an IFERROR function:
IFERROR( [main formula] ,0) This is done to force a return of 0 if an error is encountered. For example, an error would be encountered if the logical conditions are not met, which implies that no trigger points have been achieved, so this conveniently gives the desired result of 0.

The main formula inside the IFERROR wrapping is given by:
[main formula] = INDEX(Table!$B$2:$G$17,MATCH(A2,Table!$A$2:$A$17,0),AGGREGATE(14,6,(COLUMN(Table!$B$1:$G$1)-COLUMN(Table!$A$1))/((LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B$2:$G$17<=B2)*(Table!$A$2:$A$17=A2)),1)+1)

which follows the standard form of INDEX( [array], [row number], [column number] ) where...

[array] = Table!$B$2:$G$17 The entire range of your trigger points and associated values in your main data table.

[row number] = MATCH(A2,Table!$A$2:$A$17,0) Looks at the Agreement Type (Cr code) in column A (A2 in this case) and finds where in column A of your main data Table that same value is found, returning the relative list position of the matching Cr code. This tells us which row that cuts across the main data table is of interest.

[column number] = AGGREGATE(14,6,(COLUMN(Table!$B$1:$G$1)-COLUMN(Table!$A$1))/((LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B$2:$G$17<=B2)*(Table!$A$2:$A$17=A2)),1)+1)
This can be broken down into several components...the top level AGGREGATE function takes the form...
= AGGREGATE(14,6, [array*], 1) which means the following:
14 returns large values in array*, similar to the LARGE function...a 15 would return small values in array*, similar to the SMALL function.
Which large value to return is described by the last parameter--in this case a 1--so we are returning the largest value in array*.
The 6 means to ignore any errors that are found in array*...this is critical because of what is done to form array* where some errors are intentionally created (more on this later).

So the messy part is describing array*, which is given by...
[array*] = (COLUMN(Table!$B$1:$G$1)-COLUMN(Table!$A$1)) / ((LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B$2:$G$17<=B2)*(Table!$A$2:$A$17=A2))
Let's take the numerator of array* first: (COLUMN(Table!$B$1:$G$1)-COLUMN(Table!$A$1)) This does nothing more than create an array {1,2,3,4,5,6}. You could even hardwire that simpler array directly into the formula, but for the sake of understanding how the array relates to the data table, I prefer to use references that are more meaningful. In this case, we want to determine which of the columns in the main data table hold the values of interest, and those columns are either B,C,D,E,F, or G. So COLUMN(Table!$B$1:$G$1) creates an array {2,3,4,5,6,7}, and the function directly references the columns under consideration (a benefit). To make an adjustment to this array so that we can use it as a column indexing array, we subtract the column number that immediately precedes this range...COLUMN(Table!$A$1), which yields a 1. So the final result is an array {1,2,3,4,5,6} that can be used as a column index once we figure out which column to use.

The denominator of array* is: ((LEFT(Table!$B$1:$G$1,4)="Trig")*(Table!$B$2:$G$17<=B2)*(Table!$A$2:$A$17=A2))
You are familiar with all of these, as they are in the formulas you posted.
(LEFT(Table!$B$1:$G$1,4)="Trig") Takes the leftmost 4 characters in the column headings Table!$B$1:$G$1 and performs a logical test to determine if those characters are "Trig". Since the six columns are labeled alternately with a Trigger number and a corresponding value, the result is an array {FALSE,TRUE,FALSE,TRUE,FALSE,TRUE}. Note the commas...these represent column breaks.

(Table!$B$2:$G$17<=B2) Performs a logical test on the entire numerical contents of the main data table to check whether the Amount in column B of the Answer sheet (for a particular row) equals or exceeds the main data table values, returning TRUE when the condition is met, and FALSE otherwise. This logical test essentially generates a matrix, and even the columns whose headings are T1, T2, and T3 (holding values of 3's, 6's, and 12's are caught up in this full table test). That's not ideal, but it doesn't affect the answer.

(Table!$A$2:$A$17=A2) This is similar to the expression used above where [row number] is described. We perform a logical test on column A of the main data table to determine which one matches the queried column A value on the Answer sheet. In this case, the formula refers to A2 (which is Cr1), and Cr1 is found on the main Table sheet in A2 (also)...so the result of this logical test is an array that looks like {TRUE;FALSE;FALSE;...;FALSE} Note the semicolons...these are row breaks.

When we multiply all three together (the column array * the row array * the matrix), two things happen. The TRUE's and FALSE's are coerced to 1's and 0's, respectively, and the result is a matrix of the same size as the main data table B2:G17...so 6 columns and 16 rows...consisting of mostly 0's. The exception will be at most one row where all of the logical conditions are satisfied:
1) we found a matching Cr number, 2) we found some columns whose headings begin with "trig", and 3) we found some amounts in the three Trig columns that are less than our queried Amt on the Answer sheet. That particular row in the matrix will have at least one "1" in it, and perhaps two or three 1's. It is also conceivable that the Amt queried does not satisfy any of the Trig columns criteria, in which case we'll have 0's corresponding to the Trig columns.

Finally we divide the numerator array {1,2,3,4,5,6} by this larger (mostly 0) matrix, and we get another matrix consisting of only columns numbers where the conditions are met and #DIV/0! errors everywhere else. Recall all of those matrix elements where the conditions were not met...those were converted to 0's, and then this last division by those 0's intentionally creates these errors. That's okay, because the "6" parameter in the AGGREGATE function says to ignore these errors. Then since we want the largest trigger condition that has been satisfied, we've used "1" as the fourth parameter in the AGGREGATE function to return the largest value in array*, which is the column index for the largest Trigger point satisfied. We actually want the next column to the right, so a +1 is added to the end of the AGGREGATE formula.

So jumping back to the INDEX function, the results of these intermediate functions are parameters used in the INDEX formula:
[main formula] = INDEX( Table!$B$2:$G$17, [row from the MATCH component] , [column number from the AGGREGATE component] )

Thank you for the explanation and your prompt response.

Biz

 

[Biz]

So here is another formula of similar length that is applicable to excel 2010 and higher.

Biz.xlsm
	A	B	C
1	Agreement Type	Amt	Result
2	Cr1	19,000	3
3	Cr2	25,000	6
4	Cr3	19,000	0
5	Cr4	26,000	6
6	Cr5	100,000	12
7	Cr6	4,000	0
8	Cr7	24,000	3
9	Cr8	25,000	6
10	Cr9	19,000	0
11	Cr10	26,000	6
12	Cr11	100,000	12
13	Cr12	4,000	0
14	Cr13	65,000	12
15	Cr14	60,000	12
16	Cr15	60,000	12
17	Cr16	60,000	6
Answer

Cell Formulas
Range	Formula
C2:C17	C2	=IFERROR(AGGREGATE(14,6,Table!C$2:G$17/((Table!A$2:A$17=A2)*(LEN(Table!C$1:G$1)=2)*(Table!B$2:F$17<=B2)*(LEN(Table!B$1:F$1)>2)),1),0)

Hi Peter,

Really love the shorter and alternative solution using aggregate function.

I managed to derive another alternative, which I plan to use in my project.
Cell C1

=IFERROR(AGGREGATE(14,6,Table1!C$2:G$17/((Table1!A$2:A$17=A2)*(Table1!B$2:F$17<=B2)*(LEFT(Table1!$B$1:$G$1,4)="Trig")),1),0)

Biz

 

[Peter_SSs]

I managed to derive another alternative, which I plan to use in my project.
Cell C1

=IFERROR(AGGREGATE(14,6,Table1!C$2:G$17/((Table1!A$2:A$17=A2)*(Table1!B$2:F$17<=B2)*(LEFT(Table1!$B$1:$G$1,4)="Trig")),1),0)

OK, that's basically the identical formula just using "Trig" instead of the length of the heading to determine the relevant columns.

 

[Biz]

Hi Peter,

I was trying to document how the formula below works.

=IFERROR(AGGREGATE(14,6,Table1!$C$2:$G$17/((Table1!A$2:A$17=$A2)*(Table1!$B$2:$G$17<=$B2)*(LEFT(Table1!$B$1:$G$1,4)="Trig")),1),0)

Breakup of formula
1) Table1!$C$2:$G$17 - You can't select $B$2 as start reference, as our desired Trigger answer starts from Column C. Hence, we start from $C$2
2) (Table1!$A$2:$A$17=$A2) - Row Match
3) (Table1!$B$2:$G$17<=$B2) - Amounts Criiteria
4) (LEFT(Table1!$B$1:$G$1,4)="Trig")) - Match Trig

The only thing confuses me Table1!$C$2:$G$17, which I believe is that you can't select $B$2 as start reference, as our desired Trigger answer starts from Column C. Hence, we start from $C$2.
Does it make sense?

Kind Regards

Biz

 

[KRice]

Thanks for the improvements on the approach. I guess I overthought things by going back to the INDEX function, when the AGGREGATE function offered everything necessary for the answer. Biz, about your last point...in my earlier detailed explanation, I mention adding 1 to the result of the AGGREGATE function (which in that example, it was returning a column index) because we want to shift the actual return by one column (we want the value corresponding to a particular trigger point, rather than the trigger point).

The implementation you describe with Table1!$C$2:$G$17 in the numerator aims to return the value desired rather than the column number, so some strategy is needed to effectively shift by one column. That's why the numerator and denominator reference the table two ways, shifted by one column: C:G in numerator and B:G in denominator. However, note that this leads to a strange mathematical operation because we are dividing a 5 column "array" by a 6 column one. That produces a matrix holding just a few values of interest and everything else in the matrix is one of two types of errors: either #DIV/0! or #N/A. The reason for the former was described earlier. The reason for the latter, which occurs in the last column of the resultant matrix, is due to the fact that there is no corresponding value to support the division operation. You could "clean up" this issue but there is probably more potential harm than good. For example, you could change the numerator to Table1!$C$2:$H$17, but that potentially introduces other values (that are not supposed to be in your table) into the calculation, but it does keep things tidy in terms of dividing a 6 column array by another 6 column array. Since the errors are ignored by AGGREGATE due to the "6" parameter, it is probably better to keep it as is with a note that the apparent mismatch is done to shift the return by one column.

 

[Biz]

KRice,
Thank you for clarifying the last point. Index and Aggregate can be useful, when the resultant answer contains texts instead of numbers.

This forum is a very amazing place with lots of talent people who share their knowledge and help people.

Kind Regards

Biz