Filter or Lookup Formula to Organize Keywords

MKTGCLOUD · #1 11-26-2018, 05:55 PM

Quote:

Originally Posted by ArviLaanemets

Maybe a single column (and formula) will do?

Code:

=IF(
     IFERROR(1*(FIND("doctor",$A2)>0),0)+IFERROR(2*(FIND("professor",$A2)>0),0)=0;
     "",
     INDEX(
          {"doctor";"professor";"doctor and professor"},
          IFERROR(1*(FIND("doctor",$A2)>0),0)+IFERROR(2*(FIND("professor",$A2)>0),0)
     )
)

Thanks for the formula. This works on a singular column, but need to distinguish those keywords with a "doctor" and "professor" in it.

xor · #2 11-27-2018, 01:49 AM

What about

MKTGCLOUD · #3 11-27-2018, 07:35 PM

Quote:

Originally Posted by xor

What about

Thanks for this. But, the formula only identifies which cell has the word “doctor” and/or “professor”.

ArviLaanemets · #4 11-27-2018, 02:31 AM

Quote:

Originally Posted by MKTGCLOUD

... but need to distinguish those keywords with a "doctor" and "professor" in it.

The formula returns either "doctor", "professor", or "doctor and professor". You set the autofilter for table on - you always can get a list of entries having "doctor" in them by setting the filter for formula column to "doctor" and "doctor and professor", or a list of entries having "professor" in them by setting the filter for formula column to "professor" and "doctor and professor", etc.

MKTGCLOUD · #5 11-27-2018, 07:37 PM

Quote:

Originally Posted by ArviLaanemets

The formula returns either "doctor", "professor", or "doctor and professor". You set the autofilter for table on - you always can get a list of entries having "doctor" in them by setting the filter for formula column to "doctor" and "doctor and professor", or a list of entries having "professor" in them by setting the filter for formula column to "professor" and "doctor and professor", etc.

Thanks for the reply, Arvi. Can you attach your version of the worksheet so I can see and reference to?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need help with lookup formula	tristanlau	Excel	1	08-14-2017 07:16 AM
Formula help please (lookup across multiple sheets)	froggybsb03	Excel	2	03-16-2017 02:16 AM
lookup or filter data, update it and save it	fre_	Excel Programming	3	06-06-2016 08:54 PM
LOOKUP - Complex lookup with 2 lookups in 1 cell	sglandon	Excel	6	05-05-2016 09:44 AM
Best way to organize resources	Steve.	Project	3	05-17-2014 07:07 AM