IF and Search function combined

%3CLINGO-SUB%20id%3D%22lingo-sub-145417%22%20slang%3D%22en-US%22%3EIF%20and%20Search%20function%20combined%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145417%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20been%20attempting%20to%20create%20a%20formula%20to%20help%20me%20streamline%20a%20process%20for%20what%20feels%20like%20forever.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20want%20to%20search%20for%20text%20(from%20a%20list%20of%20texts)%20within%20a%20cell%2C%20and%20then%20if%20that%20text%20exists%2C%20return%20that%20text.%20The%20list%20of%20texts%20is%20approx%20150%20names%20long.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20for%20example%2C%20say%20the%20following%20cells%20have%20the%20following%20text%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA1%20-%20%22Car%20Insurance%20Joe%20Bloggs%20Jan-18%22%3C%2FP%3E%0A%3CP%3EA2%20-%20%22Salary%20recharge%20J%20Shaw%20Dec-17%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20want%20to%20insert%20a%20formula%20in%20B1%20that%20says%20if%20any%20name%20from%20my%20given%20list%20(which%20is%20a%20list%20of%20names%2C%20and%20has%20'bloggs'%20and%20'Shaw'%20in%20it%20(with%20another%20150%20names%20in%20that%20list)%20appears%20in%20the%20selected%20cell%20(i.e.%20A1)%2C%20then%20return%20the%20value%20that%20matches%2C%20i.e.%20'Bloggs'.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20anybody%20know%20if%20this%20is%20possible%3F%20I've%20been%20trying%20for%20hours%20and%20can't%20work%20it%20out!%20I%20get%20close%2C%20which%20is%20why%20I'm%20adamant%20it's%20possible!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMany%20thanks%20in%20advance%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%0A%3CP%3EAbi%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-145417%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERequests%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145894%22%20slang%3D%22en-US%22%3ERE%3A%20IF%20and%20Search%20function%20combined%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145894%22%20slang%3D%22en-US%22%3EThanks%20so%20much%20Sergei%20-%20this%20worked%20perfectly!%20I%20regret%20wasting%20hours%20and%20not%20posting%20here%20sooner%20now%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Thanks%20again%20Abi%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145428%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20and%20Search%20function%20combined%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145428%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20abi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20here%20is%20how%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fget-first-match-cell-contains%26nbsp%3B%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fget-first-match-cell-contains%26nbsp%3B%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi all,

 

I've been attempting to create a formula to help me streamline a process for what feels like forever. 

 

I want to search for text (from a list of texts) within a cell, and then if that text exists, return that text. The list of texts is approx 150 names long.

 

So for example, say the following cells have the following text:

 

A1 - "Car Insurance Joe Bloggs Jan-18"

A2 - "Salary recharge J Shaw Dec-17"

 

I want to insert a formula in B1 that says if any name from my given list (which is a list of names, and has 'bloggs' and 'Shaw' in it (with another 150 names in that list) appears in the selected cell (i.e. A1), then return the value that matches, i.e. 'Bloggs'.

 

Does anybody know if this is possible? I've been trying for hours and can't work it out! I get close, which is why I'm adamant it's possible!

 

 

Many thanks in advance :)

Abi

 

 

2 Replies
Highlighted
Highlighted
Thanks so much Sergei - this worked perfectly! I regret wasting hours and not posting here sooner now :) Thanks again Abi