Formula for most popular item per customer

[mharri]

Hi

	A	B	C	D	E	F
1​		Orange	Apple	Grapefruit	Banana	Most Popular
2​	Customer 1	2	5	9	6	Grapefruit
3​	Customer 2	3	7	2	4	Apple

This is a simplified version of a spreadsheet that has 2000 lines

What I'im looking for is a formula in Column F that will advise the most popular fruit for each Customer

Thanks

 

[jasonb75]

Try

=INDEX(B$1:E$1,MATCH(MAX(B2:E2),B2:E2,0))

 

[RoryA]

Please make a bit more of an effort with your thread title in future. "Help" is really not at all useful. Thanks.

 

[Peter_SSs]

I suggest that you update your Account details (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’)

Assuming you have Excel 365 with FILTER function, then

20 07 16.xlsm
	A	B	C	D	E	F
1		Orange	Apple	Grapefruit	Banana	Most Popular
2	Customer 1	2	5	9	6	Grapefruit
3	Customer 2	3	7	2	4	Apple
4	Customer 3	4	2	4	4	Orange, Grapefruit, Banana
Most Popular

Cell Formulas
Range	Formula
F2:F4	F2	=TEXTJOIN(", ",1,FILTER(B$1:E$1,B2:E2=MAX(B2:E2)))

If you don't have the FILTER function but do have TEXTJOIN, then try

20 07 16.xlsm
	A	B	C	D	E	F
1		Orange	Apple	Grapefruit	Banana	Most Popular
2	Customer 1	2	5	9	6	Grapefruit
3	Customer 2	3	7	2	4	Apple
4	Customer 3	4	2	4	4	Orange, Grapefruit, Banana
Most Popular (2)

Cell Formulas
Range	Formula
F2:F4	F2	=TEXTJOIN(", ",1,IF(B2:E2=MAX(B2:E2),B$1:E$1,""))

 

[mharri]

Apologies

I'm using Excel 2019, it doesn't have FILTER so i tried the TEXTJOIN option but that resulted in a #VALUE!. I've tried the online Excel which does have FILTER and that worked prefectly

Also tried the INDEX option and that worked as well

Apolgies for the bad subject tile and I have updated my profile



Thanks for your help

 

[Fluff]

As you are on 2019 you will need to confirm Peter's 2nd formula with Ctrl Shift Enter, rather than just Enter.

 

[mharri]

Thanks Fluff, that has also worked

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

Sorry, forgot to mention the C+S+E entry.

Thanks for updating your profile though.