+ Reply to Thread
Results 1 to 11 of 11

How to find data with partial match in same tab different column

  1. #1
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    How to find data with partial match in same tab different column

    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
    Attached Files Attached Files
    Last edited by rayted; 08-14-2020 at 11:16 AM.
    Thanks,

    R.



  2. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: How to find data with partial match in same tab different column

    Hello rayted:

    Put this in C2 and copy down.
    Please Login or Register  to view this content.
    Or:
    Please Login or Register  to view this content.
    Pete

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: How to find data with partial match in same tab different column

    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

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: How to find data with partial match in same tab different column

    Quote Originally Posted by PeteABC123 View Post
    Hello rayted:

    Put this in C2 and copy down.
    Please Login or Register  to view this content.
    Or:
    Please Login or Register  to view this content.
    Pete
    I was looking at that as well, but check out row 90, doesn't work, even using =countif() misses it.

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: How to find data with partial match in same tab different column

    =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).
    Attached Files Attached Files
    Last edited by Limor_OP; 08-14-2020 at 11:41 AM.

  6. #6
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: How to find data with partial match in same tab different column

    thank you all, this helps a lot! rep added

  7. #7
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: How to find data with partial match in same tab different column

    Try this in C2

    Please Login or Register  to view this content.
    Pete

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: How to find data with partial match in same tab different column

    Quote Originally Posted by davesexcel View Post
    I was looking at that as well, but check out row 90, doesn't work, even using =countif() misses it.
    just noticed that as well in my formula, and changed mine to capture numbers as well...
    Thanks for discerning...

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: How to find data with partial match in same tab different column

    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.

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: How to find data with partial match in same tab different column

    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

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How to find data with partial match in same tab different column

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need formula to find partial string match in a separate column and return adjacent value.
    By majesticoj in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-27-2019, 10:28 AM
  2. Match columns A & C and partial Match B & D and get column E data
    By Excelbanksters in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-26-2016, 04:05 PM
  3. [SOLVED] I need you!: Can I find/match cell in column then paste partial row?
    By CastingDirector in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-07-2015, 03:47 PM
  4. find data based on partial match
    By mikoski in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2015, 08:47 AM
  5. [SOLVED] Find the row number with a partial match to text in column A
    By zookeepertx in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-10-2014, 01:39 PM
  6. Find Partial Text Match between Two Columns of data
    By samybelen in forum Excel General
    Replies: 1
    Last Post: 10-23-2013, 01:45 PM
  7. Indicate a partial match from a column of data
    By webbmeister in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 06:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1