Need Help simplifying a formula!

[RICH937]

Hi Excel people! I am sure I have overcomplicated this, but after 3 days of trying and failing to write a formula that works, this is where I landed. It works, but it's way too long, and is causing "calculate sheet" to take an extremely long time. An excel sheet with both the data I'm using and the steps I used to create the final formula is attached. The data I am using has 34 products sold in118 categories to ~10.5K customers. I need to identify a specific value for each product in each category associated with an adjusted %. The problem is the adjusted % occurs multiple times across products/categories. I have tried every XLookup, Index+Match, "If", and Filtering combination I could think of to ID the specific value by product+category, but every attempt I made returned an earlier instance. I finally ended up turning each Category + Product into an individual array, and then using index, filter, choosecols and xlookup to ID the correct value. It works, but it's insanely long.

Note: I couldn't get the mini-sheet to work. Both the "mini-sheet" and "table only" options were grayed out, so I uploaded the excel to my google drive. here's a link to the file folder. It should be set up to download or view in google docs: excel help - Google Drive

 

[RICH937]

Thought this might help too:
S.1 Formula: FILTER(CHOOSECOLS(INDEX($B$2:$G$21331,,),5,6),($B$2:$B$21331=B3)*($C$2:$C$21331=C3))
S.2 Formula: XLOOKUP((MIN(CHOOSECOLS(INDEX(J3#,,),1))),CHOOSECOLS(INDEX(J3#,,),1),CHOOSECOLS(INDEX(J3#,,),2))
S.3 Formula: XLOOKUP((MIN(CHOOSECOLS(INDEX((FILTER(CHOOSECOLS(INDEX($B$2:$G$21331,,),5,6),($B$2:$B$21331=B3)*($C$2:$C$21331=C3))),,),1))),CHOOSECOLS(INDEX((FILTER(CHOOSECOLS(INDEX($B$2:$G$21331,,),5,6),($B$2:$B$21331=B3)*($C$2:$C$21331=C3))),,),1),CHOOSECOLS(INDEX((FILTER(CHOOSECOLS(INDEX($B$2:$G$21331,,),5,6),($B$2:$B$21331=B3)*($C$2:$C$21331=C3))),,),2))

The third formula is the same as the second with the first formula swapped for every instance of J3#.

Really appreciate any help!

 

[RICH937]

 

[KRice]

Have a look at this to see if it helps:

20230603_EXCEL HELP.xlsx
	B	C	D	E	F	G
1	CATEGORY (C)	PRODUCT (P)	GROUP (G)	QTY	PERCENT (%)	C+P COUNT
2	CATEGORY 1	PRODUCT 1	GROUP 1	40	20%	1
3	CATEGORY 1	PRODUCT 2	GROUP 2	200	36%	1
4	CATEGORY 1	PRODUCT 2	GROUP 3	150	2%	2
5	CATEGORY 1	PRODUCT 2	GROUP 4	100	20%	3
6	CATEGORY 1	PRODUCT 3	GROUP 5	13,400	11%	1
7	CATEGORY 1	PRODUCT 3	GROUP 6	550	15%	2
8	CATEGORY 1	PRODUCT 3	GROUP 7	400	18%	3
9	CATEGORY 1	PRODUCT 3	GROUP 8	225	19%	4
10	CATEGORY 1	PRODUCT 3	GROUP 9	100	20%	5
11	CATEGORY 1	PRODUCT 4	GROUP 7	400	44%	1
12	CATEGORY 1	PRODUCT 4	GROUP 1	400	9%	2
13	CATEGORY 1	PRODUCT 4	GROUP 10	125	2%	3
14	CATEGORY 1	PRODUCT 4	GROUP 11	75	9%	4
15	CATEGORY 1	PRODUCT 4	GROUP 12	50	13%	5
16	CATEGORY 1	PRODUCT 4	GROUP 13	25	16%	6
17	CATEGORY 1	PRODUCT 4	GROUP 14	25	18%	7
18	CATEGORY 1	PRODUCT 4	GROUP 15	25	20%	8
19	CATEGORY 1	PRODUCT 5	GROUP 8	425	45%	1
20	CATEGORY 1	PRODUCT 5	GROUP 16	375	15%	2
21	CATEGORY 1	PRODUCT 5	GROUP 17	150	2%	3
22	CATEGORY 1	PRODUCT 5	GROUP 18	100	6%	4
23	CATEGORY 1	PRODUCT 5	GROUP 19	75	12%	5
24	CATEGORY 1	PRODUCT 5	GROUP 20	50	16%	6
25	CATEGORY 1	PRODUCT 5	GROUP 21	25	18%	7
26	CATEGORY 1	PRODUCT 5	GROUP 22	25	20%	8
Sheet1 (2)

20230603_EXCEL HELP.xlsx
	N	O	P
20			C&P Count for Min%
21	CATEGORY 1	PRODUCT 1	1
22	CATEGORY 1	PRODUCT 2	2
23	CATEGORY 1	PRODUCT 3	1
24	CATEGORY 1	PRODUCT 4	3
25	CATEGORY 1	PRODUCT 5	3
Sheet1 (2)

Cell Formulas
Range	Formula
N21:O25	N21	=UNIQUE(B2:C26)
P21:P25	P21	=LET(fltfg,FILTER($F$2:$G$26,($B$2:$B$26=$N21)*($C$2:$C$26=$O21)*($F$2:$F$26)),XLOOKUP(MIN(INDEX(fltfg,,1)),INDEX(fltfg,,1),INDEX(fltfg,,2)))
Dynamic array formulas.

 

[Peter_SSs]

Note: I couldn't get the mini-sheet to work. Both the "mini-sheet" and "table only" options were grayed out,

Look here: Xl2bb is disabled

 

[Fluff]

Another option is

Fluff.xlsm
	B	C	D	E	F	G	H	I	J	K
1	CATEGORY (C)	PRODUCT (P)	GROUP (G)	QTY	PERCENT (%)	C+P COUNT
2	CATEGORY 1	PRODUCT 1	GROUP 1	40	20%	1		CATEGORY 1	PRODUCT 2	2
3	CATEGORY 1	PRODUCT 2	GROUP 2	200	36%	1
4	CATEGORY 1	PRODUCT 2	GROUP 3	150	2%	2
5	CATEGORY 1	PRODUCT 2	GROUP 4	100	20%	3
6	CATEGORY 1	PRODUCT 3	GROUP 5	13400	11%	1
7	CATEGORY 1	PRODUCT 3	GROUP 6	550	15%	2
8	CATEGORY 1	PRODUCT 3	GROUP 7	400	18%	3
9	CATEGORY 1	PRODUCT 3	GROUP 8	225	19%	4
10	CATEGORY 1	PRODUCT 3	GROUP 9	100	20%	5
11	CATEGORY 1	PRODUCT 4	GROUP 7	400	44%	1
12	CATEGORY 1	PRODUCT 4	GROUP 1	400	9%	2
13	CATEGORY 1	PRODUCT 4	GROUP 10	125	2%	3
14	CATEGORY 1	PRODUCT 4	GROUP 11	75	9%	4
15	CATEGORY 1	PRODUCT 4	GROUP 12	50	13%	5
16	CATEGORY 1	PRODUCT 4	GROUP 13	25	16%	6
17	CATEGORY 1	PRODUCT 4	GROUP 14	25	18%	7
18	CATEGORY 1	PRODUCT 4	GROUP 15	25	20%	8
19	CATEGORY 1	PRODUCT 5	GROUP 8	425	45%	1
20	CATEGORY 1	PRODUCT 5	GROUP 16	375	15%	2
21	CATEGORY 1	PRODUCT 5	GROUP 17	150	2%	3
22	CATEGORY 1	PRODUCT 5	GROUP 18	100	6%	4
23	CATEGORY 1	PRODUCT 5	GROUP 19	75	12%	5
24	CATEGORY 1	PRODUCT 5	GROUP 20	50	16%	6
25	CATEGORY 1	PRODUCT 5	GROUP 21	25	18%	7
26	CATEGORY 1	PRODUCT 5	GROUP 22	25	20%	8
27
Sheet5

Cell Formulas
Range	Formula
K2	K2	=TAKE(SORT(FILTER(F2:G100,(B2:B100=I2)*(C2:C100=J2))),1,-1)

 

[KRice]

Thanks @Fluff for insight into the TAKE function. I hadn't investigated it before. Used with the SORT function in this case leads to a very nice method for extracting the desired value.

After re-examining my offering, I realized that I included an array expression that is not necessary, so a slightly shorter version of mine...

LET(fltfg,FILTER($F$2:$G$26,($B$2:$B$26=N21)*($C$2:$C$26=O21)),XLOOKUP(MIN(INDEX(fltfg,,1)),INDEX(fltfg,,1),INDEX(fltfg,,2)))

...but I like your offering much better.