Return value that appears more than once in a row

[davidthomas683070]

Hi, I've got 3 columns of data (Columns 1-3) and I'm wanting to extract a value into a column 4 if it appears more than once in that row. If a row doesn't contain the same value more than once, I don't need a value returning in column 4. Example below which shows the value that should be returned in column 4. If anyone can help it would be much appreciated!

 

[Legacy 143009]

=IFERROR(INDEX($A1:$C1,MATCH(2,($A1:$C1=$A1)+($A1:$C1=$B1)+($A1:$C1=$C1),0)),"")

This is an array formula. Paste to formula bar and press Ctrl+Shift+Enter together.

 

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

 

[davidthomas683070]

=IFERROR(INDEX($A1:$C1,MATCH(2,($A1:$C1=$A1)+($A1:$C1=$B1)+($A1:$C1=$C1),0)),"")

This is an array formula. Paste to formula bar and press Ctrl+Shift+Enter together.

Hi, thanks very much for this. It appears to work for all rows in my example other than row 3 where it returns blank. In row 3 I would want it to return 'Art, Design and Fashion'.

 

[davidthomas683070]

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

Ok I've updated my Excel version

 

[Fluff]

Ok I've updated my Excel version

Thanks for that.

How about

=LET(x,COUNTIFS(A1:C1,A1:C1),IF(MAX(x)>1,TAKE(SORTBY(A1:C1,x,-1),,1),""))

 

[Legacy 143009]

OK, this should work:

=IFERROR(INDEX($A1:$C1,MATCH(2,($A1:$C1=$A1)+($A1:$C1=$B1)+($A1:$C1=$C1),-1)),"")

 

[davidthomas683070]

Thanks for that.

How about

=LET(x,COUNTIFS(A1:C1,A1:C1),IF(MAX(x)>1,TAKE(SORTBY(A1:C1,x,-1),,1),""))

Great, that look like it's done the job. Appreciate the quick responses!

 

[davidthomas683070]

OK, this should work:

=IFERROR(INDEX($A1:$C1,MATCH(2,($A1:$C1=$A1)+($A1:$C1=$B1)+($A1:$C1=$C1),-1)),"")

This one appears to be returning blank for row 5 now but not to worry, the other formula seems to have done the trick

 

[Fluff]

Glad we could help & thanks for the feedback.