formula to find unique values

[genisys]

Dear All,

Need some help on retrieve unique values into column E. Please refer below for samples. i have tried vlookup and hlookup but can't work

[TABLE="width: 500"]
<tbody>[TR]
[TD]columnA[/TD]
[TD]columnB[/TD]
[TD]columnC[/TD]
[TD]columnD[/TD]
[TD]columnE[/TD]
[TD]reference[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]not[/TD]
[TD]not[/TD]
[TD]not[/TD]
[TD]apple[/TD]
[TD]watermelon[/TD]
[/TR]
[TR]
[TD]not[/TD]
[TD]orange[/TD]
[TD]not[/TD]
[TD]not[/TD]
[TD]orange[/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]not[/TD]
[TD]not[/TD]
[TD]banana[/TD]
[TD]not[/TD]
[TD][/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]not[/TD]
[TD]not[/TD]
[TD]not[/TD]
[TD]watermelon[/TD]
[TD][/TD]
[TD]apple[/TD]
[/TR]
</tbody>[/TABLE]

 

[Surreybloke]

So am I right in thinking you want a formula in column E, using essentially column F (reference) to see if it can find that word or not in the preceding columns across the same row?

 

[genisys]

So am I right in thinking you want a formula in column E, using essentially column F (reference) to see if it can find that word or not in the preceding columns across the same row?

Yup, you are right

 

[Surreybloke]

One suggestion, which is a bit of a half-way house towards what you want, would be a countif formula that would tell you how many times your reference word appears in the other columns along that row.

Another, would be the MATCH formula, which can tell you where in your array, it finds the specified value.

Do you think either of these would fulfill your requirement?