+ Reply to Thread
Results 1 to 8 of 8

Vlookup using parts of a string

  1. #1
    Registered User
    Join Date
    02-14-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Vlookup using parts of a string

    Hi,
    I have a problem i need help solving..
    I have two columns of data on the same spreadsheet, which look like this:
    A B
    19300617317231.jpg 19300617317231
    19300617342332.jpg 51465416516566
    19300617421297.jpg 304514010502
    19306617342363.jpg 432165651882
    1940055020068.jpg 7889195151615
    1940055020068a.jpg 9306617342363
    304514010502.jpg 648132121333
    3045140105106.jpg 4784165161221
    4025700001023.jpg 7871516513213

    I need a formula which looks up the last 11 Digits in column B and Checks that there is a match in column A which will then return the string of numbers into a new column (c)
    To show that it exists in both columns.
    Note: it is expected that not all of column A will have a valid return!
    Attached is sample of my data as this is my first time posting and i know im not being very Clear!
    In the attachment there should be 4 valid entry's that are the same.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Vlookup using parts of a string

    you can try it like this
    =iferror(VLOOKUP("*"&TRIM(RIGHT(B2,11))&"*",$A$2:$B$10,2,FALSE),"")
    Last edited by vlady; 02-14-2013 at 08:31 PM.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    02-14-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Vlookup using parts of a string

    Thanks! im pretty sure its working how i want!
    If you don't mind would you mind walking me through this formula as it is some new functions i havnt used before

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Vlookup using parts of a string

    yup..
    my bad now i look at formula i gave this is better i think twist the trim and right

    =iferror(VLOOKUP("*"&RIGHT(trim(B2),11))&"*",$A$2:$B$10,2,FALSE),"")
    starting now with trim()
    trim(B2) will get rid of the spaces before and after the text in B2
    RIGHT(B2,11) -> this will get the 11 characters from right of any number/text
    and last
    iferror(thevlookupformula,"anymessage here or value if the vlookup return error")

    regards,
    Vladimir

  5. #5
    Registered User
    Join Date
    02-14-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Vlookup using parts of a string

    Thanks for running through it!
    The new formula you gave, it displays a pop-up message saying that their are not enough arguments for it to execute?
    Help please

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Vlookup using parts of a string

    delete the extra close ) parenthesis after the number 11

  7. #7
    Registered User
    Join Date
    02-14-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Vlookup using parts of a string

    ahh! thank you again for your help!

  8. #8
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Vlookup using parts of a string

    Your vewry much welcome any time. Thanks also.

    If you have no other query for this specific Problem Can you mark this thread "Solved".

    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

    Note:
    You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
    By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.


    Regards,
    Vladimir

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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