# Return Only Data That Appears in Three (Or More) Columns

1. ## Return Only Data That Appears in Three (Or More) Columns

Hi,

I'm looking for help finding a formula that would return data that shows up in multiple columns. In the attached sheet, I have three different columns with different bakeries. I want it to return only the bakeries that are in all three of the columns - in this case, only Brenda's Bake Shop would be listed.

Thanks!

2. ## Re: Return Only Data That Appears in Three (Or More) Columns

H5
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B:B)/(1/((B:B<>"")*(COUNTIF(D:D,B:B)>0)*(COUNTIF(F:F,B:B)>0))),ROWS(H\$4:H4))),"")

But this formula is take time too long.
If you can limited range area shorter it will be better as.

H5
=IFERROR(INDEX(B\$1:B\$15,AGGREGATE(15,6,ROW(B\$1:B\$15)/(1/((B\$1:B\$15<>"")*(COUNTIF(D\$1:D\$15,B\$1:B\$15)>0)*(COUNTIF(F\$1:F\$15,B\$1:B\$15)>0))),ROWS(H\$4:H4))),"")

\$1 must start from \$1
\$15 can change for your last row of data.

Regards.

3. ## Re: Return Only Data That Appears in Three (Or More) Columns

Hi, thank you so much for your help but that's not returning anything for me. Anyone else have any ideas?

Thanks!

4. ## Re: Return Only Data That Appears in Three (Or More) Columns

I tested both of menem's formulas in cells H5:H8 and they both return Brenda's Bake Shop in cell H5 while leaving H6:H8 blank.
The AGGREGATE function is supported by versions of Excel 2010 and later and since your profile shows 2017 I don't know why it wouldn't work for you. (the first one does take a while as menem stated)
Let us know if you have any questions.

5. ## Re: Return Only Data That Appears in Three (Or More) Columns

Hi JeteMc, thanks for the response. I think the issue might be that I'm trying to use this on Google Sheets... have been trying to figure out how to make it work but it doesn't seem to go. Any thoughts? Thanks!

6. ## Re: Return Only Data That Appears in Three (Or More) Columns

Ask one of the moderators to move the thread to the "For Other Platforms(Mac, Google Docs, Mobile OS etc)" forum, where contributors with Google Sheets knowledge may see it.
Let us know if you have any questions.

7. ## Re: Return Only Data That Appears in Three (Or More) Columns

Unfortunately it looks like I can't contact the moderators yet because I'm still a new member on here :-(

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1