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

[TABLE="width: 100"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Region 1[/TD]
[TD]Sales ($millions)[/TD]
[TD]Region 2[/TD]
[TD]Sales ($millions)[/TD]
[/TR]
[TR]
[TD]Engines[/TD]
[TD]City A[/TD]
[TD]10[/TD]
[TD]City F[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]Turbo fans[/TD]
[TD]City B[/TD]
[TD]5[/TD]
[TD]City G[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Lubricants[/TD]
[TD]City C[/TD]
[TD]8[/TD]
[TD]City H[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Ball bearings[/TD]
[TD]City D[/TD]
[TD]21[/TD]
[TD]City I[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]Hydraulics[/TD]
[TD]City E[/TD]
[TD]15[/TD]
[TD]City J[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]

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?


[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Product[/td][td]Region 1[/td][td]Sales ($millions)[/td][td]Region 2[/td][td]Sales ($millions)[/td][td][/td][td]Region 2[/td][td]Engines[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Engines[/td][td]City A[/td][td]

10​

[/td][td]City F[/td][td]

42​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Turbo fans[/td][td]City B[/td][td]

5​

[/td][td]City G[/td][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Lubricants[/td][td]City C[/td][td]

8​

[/td][td]City H[/td][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Ball bearings[/td][td]City D[/td][td]

21​

[/td][td]City I[/td][td]

33​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Hydraulics[/td][td]City E[/td][td]

15​

[/td][td]City J[/td][td]

11​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

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?


[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]Product[/TD]
[TD]Region 1[/TD]
[TD]Sales ($millions)[/TD]
[TD]Region 2[/TD]
[TD]Sales ($millions)[/TD]
[TD][/TD]
[TD]Region 2[/TD]
[TD]Engines[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]Engines[/TD]
[TD]City A[/TD]
[TD]

10​

[/TD]
[TD]City F[/TD]
[TD]

42​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]Turbo fans[/TD]
[TD]City B[/TD]
[TD]

5​

[/TD]
[TD]City G[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]Lubricants[/TD]
[TD]City C[/TD]
[TD]

8​

[/TD]
[TD]City H[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]Ball bearings[/TD]
[TD]City D[/TD]
[TD]

21​

[/TD]
[TD]City I[/TD]
[TD]

33​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]Hydraulics[/TD]
[TD]City E[/TD]
[TD]

15​

[/TD]
[TD]City J[/TD]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Product[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Region 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Sales ($millions)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Region 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Sales ($millions)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Region 2[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Engines[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]City A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]City F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]42[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Engines[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Turbo fans[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]City B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]City G[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Hydraulics[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Lubricants[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]City C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]City H[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Ball bearings[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]City D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]City I[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]33[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]Hydraulics[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]City E[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]City J[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]42[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

</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.