Hello all,
Can someone help me with how to FIND data from cell b2 in column A? i need to know if it exists in column A partially! so partial data from column b, does it exist in column A? thank you
thank you
Hello all,
Can someone help me with how to FIND data from cell b2 in column A? i need to know if it exists in column A partially! so partial data from column b, does it exist in column A? thank you
thank you
Last edited by rayted; 08-14-2020 at 11:16 AM.
Thanks,
R.
Hello rayted:
Put this in C2 and copy down.Or:Please Login or Register to view this content.
PetePlease Login or Register to view this content.
have you tried something like … =VLOOKUP("*"&B2&"*",$A$2:$A$225,1,FALSE)
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
=IF(ISERROR(INDEX(A:A,SMALL(IF(TEXT(B2,0)=MID($A$1:$A$2000,4,40),ROW($A$1:$A$2000)),1))),"No","Yes")
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Last edited by Limor_OP; 08-14-2020 at 11:41 AM.
thank you all, this helps a lot! rep added
Try this in C2
PetePlease Login or Register to view this content.
This is another array formula that works...
{=SUBSTITUTE(VLOOKUP("*"&B2&"*","*"&A:A,1,FALSE),"*","")}
remember to copy as =SUBSTITUTE(VLOOKUP("*"&B2&"*","*"&A:A,1,FALSE),"*","") then activate it using ctrl+shift+enter and then auto fill down and it will get all of them.
Try to eliminate "part text" in A2, then flag the case:
=LEN(A2)>LEN(SUBSTITUTE(A2,B2,""))
Note: row 26 case
"036KNM4551I" vs "036KNM4551i"
with last character is both Upper and Lower case, my formula catch it while the others do not.
If it is treated at same ID, try:
=LEN(UPPER(A26))>LEN(SUBSTITUTE(UPPER(A26),UPPER(B26),""))
Quang PT
How about
=IF(COUNT(INDEX(SEARCH(B2,$A$2:$A$999),)),"Yes","No")
or
=LOOKUP(9,SEARCH(B2,$A$2:$A$999),$A$2:$A$999)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks