Looking for the best formula to identify best option based on 3 columns of data

[mcmuney]

Hello,
I'm needing to evaluate data in 3 columns to come up with the best result. On the below, Columns A-C has the data and Expected Results is what I need to come up with. What formula can easily achieve this? Thanks in advance.

ColumnA	ColumnB	ColumnC	Expected Result
Apple	Apple	Apple	Apple
Apple	Banana	Banana	Banana
#N/A​	Apple	#N/A​	Apple
Cherry	Apple	#N/A​	Mixed
Apple	Banana	Cherry	Mixed
#N/A​	#N/A​	#N/A​	#N/A​

 

[ISY]

Hi

=IF(MAX(COUNTIF(A2:C2,A2:C2))=1,"Mixed",INDEX(A2:C2,1,MAX(COUNTIF(A2:C2,A2:C2))))

 

[Fluff]

What version of Excel are you using?

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

 

[mcmuney]

Hi

=IF(MAX(COUNTIF(A2:C2,A2:C2))=1,"Mixed",INDEX(A2:C2,1,MAX(COUNTIF(A2:C2,A2:C2))))

Hi, this worked perfect on the sample data, but when the data is rearranged it does not work. See example below:

ColumnA	ColumnB	ColumnC	Expected Result	Formula	Note
Apple	Apple	Apple	Apple	Apple	works
Apple	Banana	Banana	Banana	Banana	works
#N/A​	Apple	#N/A​	Apple	Apple	works
Cherry	Apple	#N/A​	Mixed	Mixed	works
Apple	Banana	Cherry	Mixed	Mixed	works
#N/A​	#N/A​	#N/A​	#N/A​	#N/A​	works
Apple	Banana	Apple	Apple	Banana	does not work
#N/A​	#N/A​	Banana	Banana	#N/A​	does not work

 

[Sufiyan97]

Hi its working for me,
Please check your formula ranges

Book4
	A	B	C	D	E	F	G	H
1	ColumnA	ColumnB	ColumnC	Expected Result	Formula	Note	Formula
2	Apple	Apple	Apple	Apple	Apple	works	Apple
3	Apple	Banana	Banana	Banana	Banana	works	Banana
4	#N/A	Apple	#N/A	Apple	Apple	works	Apple
5	Cherry	Apple	#N/A	Mixed	Mixed	works	Mixed
6	Apple	Banana	Cherry	Mixed	Mixed	works	Mixed
7	#N/A	#N/A	#N/A	#N/A	#N/A	works	#N/A
8	Apple	Banana	Apple	Apple	Banana	does not work	Apple
9	#N/A	#N/A	Banana	Banana	#N/A	does not work	#N/A
10
Sheet1

Cell Formulas
Range	Formula
G2:G9	G2	=IF(MAX(COUNTIF(A2:D2,A2:D2))=1,"Mixed",INDEX(A2:D2,1,MAX(COUNTIF(A2:D2,A2:D2))))

 

[mcmuney]

Hi its working for me,
Please check your formula ranges

Book4
	A	B	C	D	E	F	G	H
1	ColumnA	ColumnB	ColumnC	Expected Result	Formula	Note	Formula
2	Apple	Apple	Apple	Apple	Apple	works	Apple
3	Apple	Banana	Banana	Banana	Banana	works	Banana
4	#N/A	Apple	#N/A	Apple	Apple	works	Apple
5	Cherry	Apple	#N/A	Mixed	Mixed	works	Mixed
6	Apple	Banana	Cherry	Mixed	Mixed	works	Mixed
7	#N/A	#N/A	#N/A	#N/A	#N/A	works	#N/A
8	Apple	Banana	Apple	Apple	Banana	does not work	Apple
9	#N/A	#N/A	Banana	Banana	#N/A	does not work	#N/A
10
Sheet1

Cell Formulas
Range	Formula
G2:G9	G2	=IF(MAX(COUNTIF(A2:D2,A2:D2))=1,"Mixed",INDEX(A2:D2,1,MAX(COUNTIF(A2:D2,A2:D2))))

The formula doesn't work on the last 2 rows.
The formula should include columns A:C only.
Column D is the results I'm looking for and Column E is what the formula is producing.
Even in your version, row9 is wrong, it should be Banana, but showing #N/A.