Solved How can you use search with a list of values?

December 30, 2014 at 18:40:35
Specs: Windows 7
For example, I would like to use a list of programs (ie Word, Excel, ect) to search within the description. I've tried, search and vlookup and it's not working properly. Ideally, i would like the search to return, Excel, Word, ect when my list of programs are found within the description. Thanks in advance.

Column A (Description)
xxxxOraclexxxx
1111Excelxxxx
Word
unknown


See More: How can you use search with a list of values?

Report •


✔ Best Answer
December 31, 2014 at 17:42:29
Here is a revised version.

The FIND command is Case Sensitive, so Oracle and oracle are not the same to FIND

The SEARCH command is Case Insensitive, so Oracle and oracle will match.

=IFERROR(INDEX(MyList,MAX(IF(ISERROR(SEARCH(MyList,A1)),-1,1)*(ROW(MyList)-ROW($C$1)+1))),"No Match")

MIKE

http://www.skeptic.com/



#1
December 30, 2014 at 19:45:02
How long is your list of programs? If it's not too long, a Nested IF using SEARCH should work.

Assume your "Descriptions" starting in A1 and your list of programs starting in B1, enter this in C1 and drag it down. Note: as written, it will work for 3 values, returning a blank cell if none of the values in B1:B3 are found. Obviously, it can be expanded to handle a longer list of search values.

=IF(NOT(ISERROR(SEARCH($B$1,A1))),$B$1,
IF(NOT(ISERROR(SEARCH($B$2,A1))),$B$2,
IF(NOT(ISERROR(SEARCH($B$3,A1))),$B$3,"")))

I'm also assuming that only one of the search values will be found in each string Column A. If you have entries like 1234WordExcel3 and xxxExcelWordyy, you will get inconsistent results.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
December 31, 2014 at 10:38:41
Thanks for the quick response. I tried nesting, but my list is going to be too long. 20+ items and growing. Also, I'm creating this report for another user, and I wanted to make sure it was "excel friendly".

Thanks,
Lesia


Report •

#3
December 31, 2014 at 11:25:40
Well, "excel friendly" and "user friendly" are not always the same thing.

The only other way I know of to search for multiple words in the middle of text strings is to use a macro. That is going to require that macros are enabled on the user's machine, something that must be done by the user or by someone the user authorizes to enable that feature.

You cannot force macros to be enabled via a macro since that would make the system vulnerable to malicious code. You can however, design a workbook that "forces" the user to enable macros by hiding the sheets that they are interested in until they (manually) enable macros and reopen the workbook. Without macros enabled, all they would see is a "Welcome" sheet with a message telling them to enable macros if they want access to any other other sheets. Once they enable macros and reopen the workbook, code would automatically run to unhide the other sheets and hide the "Welcome" sheet. At that point, the macro to do the searching could be run.

If you are interested in a macro, let me know and I'll see what I can put together.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 31, 2014 at 14:33:20
Here is a SUMPRODUCT() function that uses a Defined Name list that might meet your requirements.

With your target string in column A

            A
1) xxxxOraclexxxx
2) 1111Excelxxxx
3) Word
4) unknown

In Column C create a Keyword List

        C
 1) Oracle
 2) Excel
 3) Word
....
10) Linux
11) Windows

Now we give our keyword list a Defined Name

First select C1 thru C11
On the ribbon select the Formula Tab
Select Define Name
In the Name box give it a name like MyList
Leave the Scope as Workbook
Check the Refers To box and make sure it contains your range of cells,
IE =Sheet1!$C$1:$C$11
Click OK

Next, in cell B1 enter the formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(MyList,A1)))>0

If any of your Keywords appear in cell A1 you will get a TRUE
If none of your Keywords appear in cell A1 you will get a FALSE

Drag down as many rows as necessary.

Your Keyword list can be as long as you need, just make sure you have
all the cells in the Defined Name.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#5
December 31, 2014 at 15:42:44
Mike,

The OP is trying to return the actual value found. Is there a way to adapt the SUMPRODUCT formula to return, Execl, Word, Linux, etc. when found?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 31, 2014 at 15:53:47
I've been trying to figure that part out, but not having much luck.

MIKE

http://www.skeptic.com/


Report •

#7
December 31, 2014 at 16:20:44
Here is an ARRAY formula.

It will return the Keyword if a match is found
and "No Match" when no match is found.

In cell B1 enter the formula:

=IFERROR(INDEX($C$1:$C$11,MAX(IF(ISERROR(FIND($C$1:$C$11,A1)),-1,1)*(ROW($C$1:$C$11)-ROW($C$1)+1))),"No Match")

And since it is an ARRAY formula, you must use CTRL-SHIFT-ENTER.

Drag down as many rows as needed.

See how that works.

MIKE

http://www.skeptic.com/


Report •

#8
December 31, 2014 at 16:26:04
You can also use a Define Name if you wish.

If you already have defined the MyList name then this should work:

=IFERROR(INDEX(MyList,MAX(IF(ISERROR(FIND(MyList,A1)),-1,1)*(ROW(MyList)-ROW($C$1)+1))),"No Match")

MIKE

http://www.skeptic.com/


Report •

#9
December 31, 2014 at 17:42:29
✔ Best Answer
Here is a revised version.

The FIND command is Case Sensitive, so Oracle and oracle are not the same to FIND

The SEARCH command is Case Insensitive, so Oracle and oracle will match.

=IFERROR(INDEX(MyList,MAX(IF(ISERROR(SEARCH(MyList,A1)),-1,1)*(ROW(MyList)-ROW($C$1)+1))),"No Match")

MIKE

http://www.skeptic.com/


Report •

#10
January 1, 2015 at 10:17:19
Thanks Mike. The sumproduct worked. If you figure out how to get it to work with the name, that would be great. :-)

Happy New Year!

Thanks again,
Lesia


Report •

#11
January 1, 2015 at 13:23:26
Did you not see my followup posts?

MIKE

http://www.skeptic.com/


Report •


Ask Question