68437

How to find if substring exists in a list of strings (and return full value in list if so)

Question:

I have a list of brand names in A5:A7655 I have a list of potential substrings in D5:D1400

I need to find a corresponding brand name for each substring where possible. Most of these substrings are contained in one of the cells in the brand name list. Returning the first brand name on the list that constains the substring is fine.

For example: My substring in D5 is "ABC Studios" - if i search my whole list I see that there is a brand name "ABC Studios, LLC". In E5 I want to return ABC Studios, LLC. If there is no match in the list returning 0 or something is fine.

I played around with =ISNUMBER(SEARCH(A5,$D$5:$D$1400)) with no success. Thanks!

Answer1:

Finding the <em>last</em> matching string is actually slightly more straightforward in terms of the formula:

=IFERROR(LOOKUP(2^15,SEARCH(D5,$A$5:$A$7655),$A$5:$A$7655),"No Match Found")

For finding the <em>first</em> matching string:

=IFERROR(INDEX($A$5:$A$7655,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(D5,$A$5:$A$7655)),,),0)),"No Match Found")

Regards

Answer2:

In <strong>E5</strong> enter:

=IFERROR(OFFSET(A5,MATCH("*" & D5 & "*",A5:A7655,0)-1,0),"")

and copy down.

Answer3:

I would suggest doing this with a two-dimensional grid. Use the brand names as the left column (the row labels), and the substrings as the top row (the column headers). Then the formula in B2 would be:

=IF(ISERROR(FIND(B$1,$A2)),B$1,$A2)

Fill that formula across and down, and the row at the bottom will be filled with the last brand name found for each substring. If no brand name is found, it will simply return the substring. (You could make it return empty instead by changing the formula in the first row after the fill down is complete - replace B$1 with "".)

Recommend

  • Scroll Bar’s scrolling bar missing
  • Pear PHP UML Class Diagrams [closed]
  • How to convert conditional formatting formula to cell-based formula for cell range?
  • Why adding from biggest to smallest floating-point numbers is less accurate than adding from smalles
  • prompt keeps coming up
  • How do I export charts from Excel as graphics
  • How do I stop a clicked combo box from clearing its value?
  • Line Graph using Flot is over Lapping
  • javascript file cordova.. how to pass value through variable
  • Conditional CSS based on background color variable inside loop
  • ASP.NET MVC 2 - Problem with Request encoding
  • CDATA getting stripped in lxml even after using strip_cdata=False
  • Crypt is different on server than on local machine
  • “Refused to load the image” in a Chrome App
  • Confusion with the order of execution when `next` with `unless` in ruby
  • Javascript fade in fade out div with youtube video
  • Route Audio song to blue-tooth programmatically?
  • c++ create an array with string::size
  • Is there a way to call library thread-local init/cleanup on thread creation/destruction?
  • Classic ASP URL Rewriting
  • Where can I get Microsoft.DirectX.dll?
  • Efficient User-Agent Regex to find Safari in Python
  • How do I Dispose a HttpResponseMessage in my Web Api Method?
  • Is it possible to run clang with llc flags
  • How can the INSERT … ON CONFLICT (id) DO UPDATE… syntax be used with a sequence ID?
  • WPF - CanExecute dosn't fire when raising Commands from a UserControl
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Google Custom Search with transparent background
  • Extracting HTML between tags
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Insert into database using onclick function
  • What is Eclipse's Declaration View used for?
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • Java applet as stand-alone Windows application?
  • Can I make an Android app that runs a web view in Chrome 39?
  • C# - Getting references of reference
  • Django query for large number of relationships
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • How to push additional view controllers onto NavigationController but keep the TabBar?