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)))))

	A	B	C	D	E	F
1	other	other	other	taxi	other	taxi
2	other	other	other	other	car	car
3	other	other	other	other	other	other

<colgroup><col width="70" span="7" style="width:52pt"> </colgroup><tbody>
</tbody>

 

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