Print Column of Values Present in all Each Other Column

[cloobless]

Hello, all. I don't seem to be able to find this specific variant of a value-matching formula.

I'm trying to output the list in column "ValueIsPresentInA-B-C", where it checks all of the other columns for values that must be present in each each of the others and then populates the column.

The problem I'm trying to solve would use either of numbers or text.

Any ideas on how this might work? Thank you for considering this problem.

[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD]LIST-A[/TD]
[TD]LIST-B[/TD]
[TD]LIST-C[/TD]
[TD]ValueIsPresentInListA-B-C[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD]pears[/TD]
[TD]peaches[/TD]
[TD]pears[/TD]
[/TR]
[TR]
[TD]guava[/TD]
[TD]lemon[/TD]
[TD]lime[/TD]
[TD]lemon[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD]blueberry[/TD]
[TD]blackberry[/TD]
[TD]apples[/TD]
[/TR]
[TR]
[TD]pears[/TD]
[TD]apples[/TD]
[TD]pears[/TD]
[TD]dragonfruit[/TD]
[/TR]
[TR]
[TD]lemon[/TD]
[TD]blackberry[/TD]
[TD]lemon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD]watermelon[/TD]
[TD]apples[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dragonfruit[/TD]
[TD]dragonfruit[/TD]
[TD]dragonfruit[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

Hi cloobless,

Below should work:

Book1
	A	B	C	D
1	LIST-A	LIST-B	LIST-C	ValueIsPresentInListA-B-C
2	apples	pears	peaches	apples
3	guava	lemon	lime	pears
4	watermelon	blueberry	blackberry	lemon
5	pears	apples	pears	dragonfruit
6	lemon	blackberry	lemon
7	peaches	watermelon	apples
8	dragonfruit	dragonfruit	dragonfruit
Sheet5

Cell Formulas
Range	Formula
D2	{=IFERROR(INDEX($A$2:$A$8,SMALL(IF(COUNTIF($B$2:$B$8,$A$2:$A$8)+COUNTIF($C$2:$C$8,$A$2:$A$8)=2,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(D$2:D2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy the formula down to D8

 

[Aryatect]

If the order is not important then this one will do the trick:

Book1
	A	B	C	D
1	LIST-A	LIST-B	LIST-C	ValueIsPresentInListA-B-C
2	apples	pears	peaches	apples
3	guava	lemon	lime
4	watermelon	blueberry	blackberry
5	pears	apples	pears	pears
6	lemon	blackberry	lemon	lemon
7	peaches	watermelon	apples
8	dragonfruit	dragonfruit	dragonfruit	dragonfruit
Sheet5

Cell Formulas
Range	Formula
D2	=IF(COUNTIF($B$2:$B$8,$A2)+COUNTIF($C$2:$C$8,$A2)=2,$A2,"")

 

[cloobless]

Hello, Aryatect! I'm sorry for replying a few hours after your reply. I was working last night and was very tired and fell asleep.

I hadn't thought about it, but yes the order is helpful and the solution works superbly. I'm able to collate a large list this way.

Thank you for taking your time to help me find this solution. I appreciate it very much. Enjoy the day.

 

[Aryatect]

Glad could help!