+ Reply to Thread
Results 1 to 6 of 6

Search for partial matches in a spreadsheet

  1. #1
    Registered User
    Join Date
    04-08-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    15

    Search for partial matches in a spreadsheet

    Hi all
    I have 2 spreadsheets in a file which I attach. In the metadata spreadsheet, I need to check if the product field of items in Yellow highlight exists as a partial match (cell to contain at least one of the words in the metadata cell) in the Product field cell of the master list spreadsheet. For example. Take row 84 of the metadata table. That's the product Valio Cheese xorhgos. In Row 7872 of the master list spreadsheet there is valio cheese which contains at least one of the words in my metadata cell. So ideally we would run this as a vlookup for partial match on column M of the metadata table.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Search for partial matches in a spreadsheet

    Which version of Excel are you using? Your profile may be out-of-date.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Contributor
    Join Date
    04-15-2013
    Location
    Singapore, Melbourne
    MS-Off Ver
    Office 365
    Posts
    167

    Re: Search for partial matches in a spreadsheet

    Please try this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Meanwhile, I suppose you need the Fuzzy Lookup Add-In for your Excel, or fuzzy matching function in Power Query.
    Row row row your boat
    Gently down the stream

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,045

    Re: Search for partial matches in a spreadsheet

    worksheet name : Metadata-2
    Cell I2 formula , Drag down and across

    HTML Code: 
    =IFERROR(VLOOKUP("*"&$E2&"*",'Master List'!$A$2:$D$8304,COLUMN(A$1),),"")

  5. #5
    Registered User
    Join Date
    04-08-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    15

    Re: Search for partial matches in a spreadsheet

    Thanks but this one won't find partial matches. For instance Row 123 in the metadata spreadsheet should read valio.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,075

    Re: Search for partial matches in a spreadsheet

    Perhaps the following will help.
    I placed the following formula in cell N2, dragged over to cell Q2 and then, while N2:Q2 were still selected, down to Q2042:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I would think that in most cases the the cell in column N will be the match for which you are looking, however I wanted to let you see what the other possible matches were also.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Userfrom to search multiple critera and have partial matches.
    By matt7416 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-18-2020, 11:27 AM
  2. [SOLVED] Want to extract partial matches from a list and add to existing list of retreived matches
    By PeteABC123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-29-2019, 02:31 PM
  3. [SOLVED] Partial matches for a teext string being used to search an array
    By Brycicle in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2018, 04:50 PM
  4. Replies: 19
    Last Post: 06-08-2017, 07:24 AM
  5. search 2 criteria and if it matches give me the number of matches
    By sxch12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-14-2017, 03:16 PM
  6. Find Partial matches
    By KTM450 in forum Excel General
    Replies: 11
    Last Post: 11-05-2015, 10:09 PM
  7. Replies: 0
    Last Post: 08-11-2012, 03:25 PM

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