Excel - Search from range, not specific cell? -


i have range of cells in column i2:i8:

wiley elsevier springer taylor sage oxford cambridge 

i want use search function on column g, it'll search 1 of values in range, , return true/false column h if finds anything. problem is, values in column g longer, , string in column substring of column g text. column g contains (for example):

blackwell publ ltd israel medical assoc journal pergamon-elsevier science ltd pergamon-elsevier science ltd mosby, inc oxford univ press cell press amer coll physicians nature publishing group cold spring harbor lab press, publications dept amer coll physicians massachusetts medical soc wiley-blackwell blackwell publishing inc amer assoc advancement science oxford univ press massachusetts medical soc oxford univ press academic press inc elsevier science academic press ltd- elsevier science ltd 

so examples, each time word wiley, oxford, elsevier etc appear in column g (such in oxford univ press or wiley-blackwell or academic press inc elsevier science), return true in column h.

i have build following functions:

=(isnumber(search(($i$2:$i$8),g2))) =(isnumber(search(($i$2:$i$2:$i$3:$i$3:$i$4:$i$4:$i$5:$i$5:$i$6:$i$6:$i$7:$i$7:$i$8:$i$8),g23))) 

but not seem work.

any suggestions?

example of wanted result:

blackwell publ ltd                          false   wiley israel medical assoc journal                false   elsevier pergamon-elsevier science ltd               true    springer pergamon-elsevier science ltd               true    taylor mosby, inc                                  false   sage oxford univ press                           true    oxford cell press                                  false   cambridge amer coll physicians                        false    nature publishing group                     false    amer coll physicians                        false    massachusetts medical soc                   false    wiley-blackwell                             true     blackwell publishing inc                    false    amer assoc advancement science              false    oxford univ press                           true     massachusetts medical soc                   false    oxford univ press                           true     academic press inc elsevier science         true     academic press ltd- elsevier science ltd    true     nature publishing group                     false    elsevier science bv                         true     mosby-elsevier                              true     massachusetts medical soc                   false    

wrap formula in sumproduct()

=sumproduct(--isnumber(search($i$1:$i$7,g1)))>0 

enter image description here


Comments

Popular posts from this blog

networking - Vagrant-provisioned VirtualBox VM is not reachable from Ubuntu host -

c# - ASP.NET Core - There is already an object named 'AspNetRoles' in the database -

ruby on rails - ArgumentError: Missing host to link to! Please provide the :host parameter, set default_url_options[:host], or set :only_path to true -