INDEX MATCH with drop down list for highest sales

[JamesonMH]

Hi folks,

I have a list of 5 products and each of the 5 products are produced in 2 different cities, with sales in each city (the cities aren't really important for the solution, just FYI).

5 of the cities (or 50% of the cities) are in a column called Region 1 and the remaining 50% are in Region 2.

I want to be able to return which product has the highest sales, depending on the Region selected from a drop down list.

Drop down list choice: Region 1 or Region 2

Product	Region 1	Sales ($millions)	Region 2	Sales ($millions)
Engines	City A	10	City F	42
Turbo fans	City B	5	City G	5
Lubricants	City C	8	City H	5
Ball bearings	City D	21	City I	33
Hydraulics	City E	15	City J	11

<tbody>
</tbody>

I want the formula to return "Ball Bearings" if I chose Region 1 (from my drop down list) and "Engines" if I chose Region 2.

I already have a formula that works (but it's inefficient) that uses INDEX/IF/MATCH/MAX/MATCH/MAX, but I want to avoid using IF statements and hopefully rely on a cleaner formula that just uses INDEX/MATCH/MAX and or something else. Arrays are ok too.

Many thanks for your time to read and help out.

James

 

[Oscar Cronquist]

I am not sure if this formula is shorter?

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Product	Region 1	Sales ($millions)	Region 2	Sales ($millions)		Region 2	Engines
2​	Engines	City A	10​	City F	42​
3​	Turbo fans	City B	5​	City G	5​
4​	Lubricants	City C	8​	City H	5​
5​	Ball bearings	City D	21​	City I	33​
6​	Hydraulics	City E	15​	City J	11​

Sheet: Sheet1

Array formula in cell H1:
=TEXTJOIN("",TRUE,IF(MAX(IF(B1:D1=G1,C2:E6))=C2:E6,A2:A6,""))

 

[Saba Sabaratnam]

Hi Jameson,

Another way to do is to use the following formula.

Copy your data table into range A1 to E6 (just to illustrate the formula)

Create a drop down list for Regions in A10

Enter the formula in B10. It is a non array formula.

=NDEX(A2:A6,MATCH(MAX((INDEX(B2:E6,0,MATCH(A10,B1:E1,0)+1))),(INDEX(B2:E6,0,MATCH(A10,B1:E1,0)+1)),0))

Let us know how you go.

Kind regards

Saba

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G
1	Product	Region 1	Sales ($millions)	Region 2	Sales ($millions)		Region 2
2	Engines	City A	10	City F	42		Engines
3	Turbo fans	City B	5	City G	5		Hydraulics
4	Lubricants	City C	8	City H	5
5	Ball bearings	City D	21	City I	33
6	Hydraulics	City E	15	City J	42
Sheet1

In G2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$6,SMALL(IF(INDEX($B$2:$E$6,0,MATCH(G$1,$B$1:$E$1,0)+1)=MAX(INDEX($B$2:$E$6,0,MATCH(G$1,$B$1:$E$1,0)+1)),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($G$2:G2))),"")

Re-written in terms of the AGGREGATE function... Just enter and copy down:

=IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/(INDEX($B$2:$E$6,0,MATCH(G$1,$B$1:$E$1,0)+1)=MAX(INDEX($B$2:$E$6,0,MATCH(G$1,$B$1:$E$1,0)+1))),ROWS($G$2:G2))),"")

 

[JamesonMH]

Hi Jameson,

Another way to do is to use the following formula.

Copy your data table into range A1 to E6 (just to illustrate the formula)

Create a drop down list for Regions in A10

Enter the formula in B10. It is a non array formula.

=NDEX(A2:A6,MATCH(MAX((INDEX(B2:E6,0,MATCH(A10,B1:E1,0)+1))),(INDEX(B2:E6,0,MATCH(A10,B1:E1,0)+1)),0))

Let us know how you go.

Kind regards

Saba

Brilliant! This is exactly what I was after. Thanks for taking the time to provide this, Saba!

James

 

[JamesonMH]

I am not sure if this formula is shorter?

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	Product	Region 1	Sales ($millions)	Region 2	Sales ($millions)		Region 2	Engines
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Engines	City A	10​	City F	42​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Turbo fans	City B	5​	City G	5​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	Lubricants	City C	8​	City H	5​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	Ball bearings	City D	21​	City I	33​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	Hydraulics	City E	15​	City J	11​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Array formula in cell H1:
=TEXTJOIN("",TRUE,IF(MAX(IF(B1:D1=G1,C2:E6))=C2:E6,A2:A6,""))

	A	B	C	D	E	F	G
1	Product	Region 1	Sales ($millions)	Region 2	Sales ($millions)		Region 2
2	Engines	City A	10	City F	42		Engines
3	Turbo fans	City B	5	City G	5		Hydraulics
4	Lubricants	City C	8	City H	5
5	Ball bearings	City D	21	City I	33
6	Hydraulics	City E	15	City J	42

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

In G2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$6,SMALL(IF(INDEX($B$2:$E$6,0,MATCH(G$1,$B$1:$E$1,0)+1)=MAX(INDEX($B$2:$E$6,0,MATCH(G$1,$B$1:$E$1,0)+1)),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($G$2:G2))),"")

Re-written in terms of the AGGREGATE function... Just enter and copy down:

=IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,(ROW($A$2:$A$6)-ROW($A$2)+1)/(INDEX($B$2:$E$6,0,MATCH(G$1,$B$1:$E$1,0)+1)=MAX(INDEX($B$2:$E$6,0,MATCH(G$1,$B$1:$E$1,0)+1))),ROWS($G$2:G2))),"")

Thanks for your quick replies Oscar and Aladin. Oscar, that formula you had is actually something similar to what I came up with originally, but a bit quicker. I was looking though to also eliminate that IF stmt. Aladin, your formula is a bit too complex for me to work with, but I thank you for your input.

 

[Saba Sabaratnam]

Happy to do it James

Saba

 

[Aladin Akyurek]

Thanks for your quick replies Oscar and Aladin. Oscar, that formula you had is actually something similar to what I came up with originally, but a bit quicker. I was looking though to also eliminate that IF stmt. Aladin, your formula is a bit too complex for me to work with, but I thank you for your input.

The issue is not complexity but correctness. It's not impossible that the two or more more products have highest sales.