+ Reply to Thread
Results 1 to 23 of 23

vlookup multiple values in single cell separated by spaces

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    vlookup multiple values in single cell separated by spaces

    Hello,

    Just wondering if its possible to vlookup individual values in a single cell to return one value...

    I have a cell that has multiple values in it separated by spaces. i would like to look up those values individually to return a single value next to that cell

    Vlookupexample.xlsx

  2. #2
    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
    52,917

    Re: vlookup multiple values in single cell separated by spaces

    Did you upload the correct file?

    You gave us this...
    A
    B
    1
    Reference Description
    2
    R114 Red
    3
    C109 Green
    4
    L202 Blue
    5
    ZD102 Yellow
    6
    MF101 Purple


    and you want this...

    A
    B
    10
    Reference Description
    11
    R114 R128 Red
    12
    C106 C109 C102 Green
    13
    L101 L202 Blue
    14
    ZD102 Yellow
    15
    MF101 MF102 Purple


    Are wwe missing some info here? (I dont see any R128 - red, for instance?)
    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

  3. #3
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: vlookup multiple values in single cell separated by spaces

    Yep, that's the correct file! there is another sheet on there that should have


    Reference Description

    R114 R128

    C106 C109 C102

    L101 L202

    ZD102

    MF101 MF102


    I want it to look at "R114 R128" and search for them individually (R114, then R128) and return the value it finds as only one of those will actually be there with a result. in this case R114 has red next to it

  4. #4
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: vlookup multiple values in single cell separated by spaces

    So there won't be a result for every value looked up, only one value in each cell will have a result. So just hoping it's possible to lookup values individually if separated by spaces and return the description of whichever of those it finds

  5. #5
    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
    52,917

    Re: vlookup multiple values in single cell separated by spaces

    Quote Originally Posted by aimeecrystalaid View Post
    Yep, that's the correct file! there is another sheet on there that should have....
    Nope sorry, it just had those 2 sheets

  6. #6
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: vlookup multiple values in single cell separated by spaces

    3 sheet example.xlsx

    try this one?

  7. #7
    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
    52,917

    Re: vlookup multiple values in single cell separated by spaces

    LOL Im sorry, the sheet WAS there in the 1st file, I just needed to scroll left (must be 1 of those days )

    Anyway, try this in sheet2, C2, copied down...
    =VLOOKUP("*"&A2&"*",'Lookup values'!$A$2:$A$6,1,0)

  8. #8
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: vlookup multiple values in single cell separated by spaces

    Haha all good, it's monday here so i'm not with it either :P

    thanks! that's close enough, my spreadsheet is a lot bigger and a bit different so i'd have to change it a bit but this will make it work now.

    can you explain what its doing though? i understand vlookup but bit confused by what it is looking up exactly - what is "*"&A2&"*" ?
    is it looking up an exact match? if one row had to look up R114 and another looked up R1148, would it get mixed up looking for just the R114 bit? if that makes sense....

  9. #9
    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
    52,917

    Re: vlookup multiple values in single cell separated by spaces

    * is a wild card, but if you are using it with a referenced cell, then you need to use "*"...I added a wild card before and after the contents of A2, so it would fond AAA R114 or R114 AAA or even AAA R114 AAA.

    And yes, unfortunately, it will also match R114 with R1144 - Im looking into that though

  10. #10
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: vlookup multiple values in single cell separated by spaces

    ahh okay, I see.

    Bummer, I wasn't sure there would be a way to do this exactly how i'd like. With the data i'm working with, i really need everything to be an EXACT match as generally there is similar things like R114, R1144 etc.

    But this formula will do the trick for now for smaller ones, will just have a look over to make sure there isn't any similar ones before i use it

    thanks heaps

  11. #11
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: vlookup multiple values in single cell separated by spaces

    Something like this hope it works...


    Azumi
    Attached Files Attached Files

  12. #12
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: vlookup multiple values in single cell separated by spaces

    well that confused me i thought you meant
    =vlookup('Lookup values'!A2,'What I''m looking up'!A1:B6,2 false)
    ie
    =vlookup("R114 R1282",'What I''m looking up'!A1:B6,2 false)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  13. #13
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: vlookup multiple values in single cell separated by spaces

    Azumi, A little bit confused by what's going on in yours? I don't really understand it

    Martin - wish it was that simple! My dilemma is that R114 and R1282 both have the same description and i would like to look up that description, but in my other sheet the only reference is one of those (eg R114) not both of them, so i can't look up an exact match.

    the biggest issue is i can't look up an approximate match either as i have craploads of data and there are similar values (eg R114, R1144)

  14. #14
    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
    52,917

    Re: vlookup multiple values in single cell separated by spaces

    hmm maybe try this...
    Use text2columns to break out the different codes, then you can search based onthem that way?

  15. #15
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: vlookup multiple values in single cell separated by spaces

    Try in 'Lookup Value' sheet B2,

    =LOOKUP(9.9E+300,SEARCH('What I''m looking up'!A$2:A$6,A2),'What I''m looking up'!B$2:B$6)

    Then copy down.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  16. #16
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: vlookup multiple values in single cell separated by spaces

    what i'm saying if that's the case vlookup wont work
    but you can do it a different way
    edit hasseeb just posted it
    but for say R114 = RED and say R1148 =BLUE
    you must sort the list by length of text smallest to largest
    Last edited by martindwilson; 12-15-2013 at 09:54 PM.

  17. #17
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: vlookup multiple values in single cell separated by spaces

    As Martin mentioned situations may be adding a <space> before & after of a cell & range could solve it without sorting.

    =LOOKUP(9.9E+300,SEARCH(" "&'What I''m looking up'!A$2:A$6&" "," "&A2&" "),'What I''m looking up'!B$2:B$6)

  18. #18
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: vlookup multiple values in single cell separated by spaces

    Sorting may work i guess, but what if i had values R114, CR114 and R1148? If it's an approximate match then the 2 longer ones may be mixed up. Basically my data contains an enormous amount of almost exactly the same information with the exception of 1 letter or number so it has to be EXACT.
    is it possible to search for values and include spaces on either side - eg " R114 " so that it eliminates any approximate matches?

  19. #19
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: vlookup multiple values in single cell separated by spaces

    Try it again, Please see the file

    Regards
    Azumi
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    08-21-2013
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: vlookup multiple values in single cell separated by spaces

    Haseeb, yours worked on the example - just can't work out how to apply it to my actual spreadsheet to see if it works

  21. #21
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: vlookup multiple values in single cell separated by spaces

    see haseebs post

  22. #22
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: vlookup multiple values in single cell separated by spaces

    or see my file....
    Attached Files Attached Files

  23. #23
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: vlookup multiple values in single cell separated by spaces

    id like to point out that i never suggested that vlookup!

+ 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] Formula to count values in single cell separated by commas
    By aimeecrystalaid in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-25-2017, 01:01 AM
  2. [SOLVED] Single cell Comma Separated Values to validation list [DIFFICULT QUESTION]
    By jgema in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-31-2013, 08:48 PM
  3. [SOLVED] How do I combine multiple lines of information into a single cell, separated by commas?
    By ilostar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2012, 11:48 AM
  4. Replies: 5
    Last Post: 06-05-2012, 03:32 PM
  5. Replies: 8
    Last Post: 11-08-2010, 10:14 AM

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