Find the odd word out

[JontyJonty]

Hello , i have 5 columns of data.

A1 = "other"
B1 = "other"
C1 = "other"
D1 = "taxi"
E1 = "other"

A2 = "other"
B2 = "other"
C2 = "other"
D2 = "other"
E2 = "car"


A3 = "other"
B3 = "other"
C3 = "other"
D3 = "other"
E3 = "other"


I am trying to work out a formula in F1, F2, F3 whereby if row contains no other word than other then put "other", otherwise put the first 'odd word'

ie in F1 it would put "taxi"
in F2 it would put "car"
in F3 it would put "other"


Thanks a lot

 

[admiral100]

Hi,

Try this:-

Ctrl+Shift+Enter NOT just Enter

F1 =IF(COUNTIF(A1:E1,"other")=5,"other",INDEX(A1:E1,,MIN(IF(A1:E1<>"other",COLUMN(A1:E1)))))

[TABLE="width: 490"]
<colgroup><col width="70" span="7" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"]A[/TD]
[TD="class: xl64, width: 70"]B[/TD]
[TD="class: xl64, width: 70"]C[/TD]
[TD="class: xl64, width: 70"]D[/TD]
[TD="class: xl64, width: 70"]E[/TD]
[TD="class: xl64, width: 70"]F[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]taxi[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]taxi[/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]car[/TD]
[TD="class: xl64"]car[/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]other[/TD]
[TD="class: xl64"]other[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In F1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,1/(1-(A1:E1="other")),A1:E1),"other")​

 

[JontyJonty]

Thanks a lot Admiral and Aladin, both work a treat.

That's awesome !

Cheers

 

[Eric W]

FYI, the 2 formulas return different results if there is more than one "odd" word.