MAX Function Formula

[acool]

Hi Everyone,

I am currently tying to create a MAX Function Formula that will provide me with an output based on two sets of criteria. In this specific example, I am trying to return the Category associated with a Brand and the Revenue. However, I would like to pull the category in which the Brand has the most revenue occurring. So in this Example, because the Brand OneUP has the most revenue occurring in the Kitchen Category, I would like to return "Kitchen" as the Category. If someone could please provide me some assistance, that would be greatly appreciated. Thank You!

 

[awoohaw]

would you be kind enough to help the forum help you?
Please use the xl2bb add in to post a mini worksheet, or at minimum post a table of the data?

Thanks in advance.

 

[awoohaw]

But try this:

Book1
	A	B	C	D	E	F	G
1	Category	brand	revenue		Brand	CATEGORY
2	Health	OneUP	2		OneUP	Kitchen	Kitchen
3	Health	Yez	5		Yez	Beauty	Beauty
4	Health	Howd	7		Howd	Health	Health
5	Health	NUE	10		NUE	Kitchen	Kitchen
6	Health	HOS	3		HOS	Beauty	Beauty
7	Kitchen	OneUP	150
8	Kitchen	Yez	0
9	Kitchen	Howd	0
10	Kitchen	NUE	35
11	Kitchen	HOS	0
12	Beauty	OneUP	20
13	Beauty	Yez	350
14	Beauty	Howd	0
15	Beauty	NUE	0
16	Beauty	HOS	700
acool

Cell Formulas
Range	Formula
G2:G6	G2	=INDEX(A2:A16,MATCH(MAX(CHOOSECOLS(FILTER(A2:C16,(B2:B16=E2)),1,3)),C2:C16,0))

 

[awoohaw]

But try this:

Book1
	A	B	C	D	E	F	G
1	Category	brand	revenue		Brand	CATEGORY
2	Health	OneUP	2		OneUP	Kitchen	Kitchen
3	Health	Yez	5		Yez	Beauty	Beauty
4	Health	Howd	7		Howd	Health	Health
5	Health	NUE	10		NUE	Kitchen	Kitchen
6	Health	HOS	3		HOS	Beauty	Beauty
7	Kitchen	OneUP	150
8	Kitchen	Yez	0
9	Kitchen	Howd	0
10	Kitchen	NUE	35
11	Kitchen	HOS	0
12	Beauty	OneUP	20
13	Beauty	Yez	350
14	Beauty	Howd	0
15	Beauty	NUE	0
16	Beauty	HOS	700
acool

Cell Formulas
Range	Formula
G2:G6	G2	=INDEX(A2:A16,MATCH(MAX(CHOOSECOLS(FILTER(A2:C16,(B2:B16=E2)),1,3)),C2:C16,0))

Obviously you can see I changed your data layout from a report into a flat file type layout.

 

[acool]

But try this:

Book1
	A	B	C	D	E	F	G
1	Category	brand	revenue		Brand	CATEGORY
2	Health	OneUP	2		OneUP	Kitchen	Kitchen
3	Health	Yez	5		Yez	Beauty	Beauty
4	Health	Howd	7		Howd	Health	Health
5	Health	NUE	10		NUE	Kitchen	Kitchen
6	Health	HOS	3		HOS	Beauty	Beauty
7	Kitchen	OneUP	150
8	Kitchen	Yez	0
9	Kitchen	Howd	0
10	Kitchen	NUE	35
11	Kitchen	HOS	0
12	Beauty	OneUP	20
13	Beauty	Yez	350
14	Beauty	Howd	0
15	Beauty	NUE	0
16	Beauty	HOS	700
acool

Cell Formulas
Range	Formula
G2:G6	G2	=INDEX(A2:A16,MATCH(MAX(CHOOSECOLS(FILTER(A2:C16,(B2:B16=E2)),1,3)),C2:C16,0))

This Worked thank you!

 

[acool]

But try this:

Book1
	A	B	C	D	E	F	G
1	Category	brand	revenue		Brand	CATEGORY
2	Health	OneUP	2		OneUP	Kitchen	Kitchen
3	Health	Yez	5		Yez	Beauty	Beauty
4	Health	Howd	7		Howd	Health	Health
5	Health	NUE	10		NUE	Kitchen	Kitchen
6	Health	HOS	3		HOS	Beauty	Beauty
7	Kitchen	OneUP	150
8	Kitchen	Yez	0
9	Kitchen	Howd	0
10	Kitchen	NUE	35
11	Kitchen	HOS	0
12	Beauty	OneUP	20
13	Beauty	Yez	350
14	Beauty	Howd	0
15	Beauty	NUE	0
16	Beauty	HOS	700
acool

Cell Formulas
Range	Formula
G2:G6	G2	=INDEX(A2:A16,MATCH(MAX(CHOOSECOLS(FILTER(A2:C16,(B2:B16=E2)),1,3)),C2:C16,0))

@awoohaw This Worked! However, I wanted to see if you knew a fix for this issue below. I added another category + plus one other brand with a value of zero for each. As I went to do the Index max on this new Brand "Yeez", it is providing me with the wrong value. Instead of "Cars, it is providing me with kitchen". Yeez is not even a value for the Kitchen Category. Was wondering if you knew why this was? Thanks for all of your help so far!

 

[Fluff]

How about

=TAKE(SORT(FILTER($A$2:$C$100,$B$2:$B$100=E2),3,-1),1,1)