# Thread: Print Column of Values Present in all Each Other Column Thanks:  2 Post #5305779 (1)Post #5305781 (1) Likes:  1 Post #5305779 (1)

1. ## Print Column of Values Present in all Each Other Column

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.

 LIST-A LIST-B LIST-C ValueIsPresentInListA-B-C apples pears peaches pears guava lemon lime lemon watermelon blueberry blackberry apples pears apples pears dragonfruit lemon blackberry lemon peaches watermelon apples dragonfruit dragonfruit dragonfruit

2. ## Re: Print Column of Values Present in all Each Other Column

Hi cloobless,

Below should work:

ABCD
1LIST-ALIST-BLIST-CValueIsPresentInListA-B-C
2applespearspeachesapples
3guavalemonlimepears
4watermelonblueberryblackberrylemon
5pearsapplespearsdragonfruit
6lemonblackberrylemon
7peacheswatermelonapples
8dragonfruitdragonfruitdragonfruit

Sheet5

Array Formulas
CellFormula
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))),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Copy the formula down to D8

3. ## Re: Print Column of Values Present in all Each Other Column

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

ABCD
1LIST-ALIST-BLIST-CValueIsPresentInListA-B-C
2applespearspeachesapples
3guavalemonlime
4watermelonblueberryblackberry
5pearsapplespearspears
6lemonblackberrylemonlemon
7peacheswatermelonapples
8dragonfruitdragonfruitdragonfruitdragonfruit

Sheet5

Worksheet Formulas
CellFormula
D2=IF(COUNTIF(\$B\$2:\$B\$8,\$A2)+COUNTIF(\$C\$2:\$C\$8,\$A2)=2,\$A2,"")

4. ## Re: Print Column of Values Present in all Each Other Column

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.