+ Reply to Thread
Results 1 to 6 of 6

Vlookup with only partial text match

  1. 06-17-2013, 02:18 PM #1
    betsy2128
    betsy2128 is offline
    Registered User
    Join Date
    06-17-2013
    Location
    nyc
    MS-Off Ver
    Excel 2007
    Posts
    1

    Vlookup with only partial text match

    Hi - I'm trying to compare two sets of data by looking up a value that only partially matches: for instance:

    In the first data set, the account is named "Production:Melchior Productions LLC:1A-Melchior Production Expenses:003A_Architect" with a value of $XX

    In the second data set, the account is named "003A_Architect" with a value of $XX

    I want to compare the two account values (they should both be the same value), but obviously can't use a vlookup because the account name is not an exact match.

    Any thoughts??
    Reply With QuoteRegister To Reply
  2. 06-17-2013, 03:19 PM #2
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Vlookup with only partial text match

    betsy, welcome to the forum.

    Are the "$XX" values you are referring to in the same cell as eg "Production:Melchior Productions LLC:1A-Melchior Production Expenses:003A_Architect"? It isn't immediately clear from your post if that's the case.

    Your best bet, in fact, is probably to upload a small sample of the data you're dealing with, with anything sensitive removed.

    Hope this helps.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.
    Reply With QuoteRegister To Reply
  3. 06-17-2013, 10:23 PM #3
    SDCh
    SDCh is offline
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: Vlookup with only partial text match

    See the attachment, the formula vlookup on sheet 2.
    Attached Files Attached Files
    Click (*) if you received helpful response.

    Regards,
    David
    Reply With QuoteRegister To Reply
  4. 06-17-2013, 10:30 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,039

    Re: Vlookup with only partial text match

    Hi and welcome to the forum

    You could use wild cards to look up data either way, but it will be easier to use the data in the 2nd data set (IF thats the 1 thats always shorter), and then find that in the 1st data set. Assuming your data is in A1 and B1 ($XX), use something like this...

    =iferror(vlookup(A1&"*",data_set1!$A$1:$B$100,2,0),"Not Found")

    This will return the value in column B for all matches based on what you have in data set 2. the iferror() will return "Not Found" if there is no match
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 06-18-2013, 07:04 AM #5
    junaid.mujtaba
    junaid.mujtaba is offline
    Registered User
    Join Date
    05-10-2012
    Location
    pakistan
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Vlookup with only partial text match

    Thank You - All, I was stucked up in similar problem using Vlookup with partial fragment; Solution is simple

    Regards,
    J
    Reply With QuoteRegister To Reply
  6. 06-18-2013, 08:03 AM #6
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Vlookup with only partial text match

    J, welcome to the forum. And thanks for sharing the good news that the "search" facility is being used :-)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1