+ Reply to Thread
Results 1 to 17 of 17

Look up Partial Cell contents

  1. #1
    Registered User
    Join Date
    09-03-2012
    Location
    Newmarket, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Look up Partial Cell contents

    I have a very large data set and I need to identify product skus and perform a v-lookup or similar to identify certain products and return a value.

    the issue I have is that I want to look up using a partial sku

    for example I have a list as below that I want to look up from and take the return value from:

    CPVP-VEE
    CPXP-CI-VPX
    CPMP-SCPRO

    but the look up will be against a series of product skus such as
    CPVP-VEE- U
    CPVP-VEE-U-HA
    CPVP-VEE-100
    CPVP-VEE-100-HA
    CPVP-VEE-U-NG
    CPVP-VEE-250
    CPVP-VEE-250-HA
    CPXP-CI-VPX-U
    CPXP-CI-VPX-100
    CPXP-CI-VPX-50
    CPMP-SCPRO-U
    CPMP-SCPRO-250
    CPMP-SCPRO-HA

    can anyone advise how I would do this?

    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Look up Partial Cell contence

    if you post a book it would be easier but using search combined with sumproduct should create a solution
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  3. #3
    Registered User
    Join Date
    09-03-2012
    Location
    Newmarket, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Look up Partial Cell contence

    thanks, I'm a newbie and appologies if I am being blonde; but how do I attach a book? I can't see any attach function. or do you just mean to add a screen shot?

  4. #4
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Look up Partial Cell contence

    Perhaps this might serve as an illustration ..

    Assuming this data is in B2:B4
    CPVP-VEE
    CPXP-CI-VPX
    CPMP-SCPRO

    Assume in E2:E14, you have the product skus
    CPVP-VEE- U
    CPVP-VEE-U-HA
    etc

    Assume that next to the product skus, in F2:F14,
    you might have numbers associated with the skus that you want to say, sum

    a. To count the skus (partial string) relevant to the data in B2:B4
    Put this in B2: =SUMPRODUCT(--ISNUMBER(SEARCH(A2,$E$2:$E$14)))
    Copy down

    b. To sum the sku's nums in F2:F14 relevant to the data in B2:B4
    Put in C2: =SUMPRODUCT(--ISNUMBER(SEARCH(A2,$E$2:$E$14)),$F$2:$F$14)
    Copy down

    ----------
    Success? Celebrate it, click the star at the bottom left of my response

  5. #5
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Look up Partial Cell contence

    to attach a book in the reply part (not quick reply but full so click go advanced then) then you can click the paper clip to upload it here then attach it

    that being said the above does what you need.

  6. #6
    Registered User
    Join Date
    09-03-2012
    Location
    Newmarket, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Look up Partial Cell contence

    example data.xlsx

    Please see attached example

    I have an asset list (column A)
    I want to identify what the upgrade path is and enter it in column B
    I have a list of partial legacy skus (column E)
    I have the upgrade path (column F)

    I want to pull the data from column F into column B.

    thanks
    Alex

  7. #7
    Registered User
    Join Date
    09-03-2012
    Location
    Newmarket, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Look up Partial Cell contence

    Sorry I meant to say thanks MAx that does recognise the partial skus but does not pull through the data I want.

    Please see attached example

    I have an asset list (column A)
    I want to identify what the upgrade path is and enter it in column B
    I have a list of partial legacy skus (column E)
    I have the upgrade path (column F)

    I want to pull the data from column F into column B.

    thanksexample data.xlsx
    Alex

  8. #8
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Look up Partial Cell contence

    im affraid to say im stumped. i can do one at a time but not all 3 at once. i hope someone else can ofer a solution so i can learn also

  9. #9
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Look up Partial Cell contence

    in b2
    =LOOKUP(2,1/(ISNUMBER(SEARCH($E$2:$E$4,A2))),$F$2:$F$4)
    and fill down
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  10. #10
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Look up Partial Cell contence

    oh oh i thought of 1 way

    Please Login or Register  to view this content.
    basicly nested ifs without the ifs. lol

    for each type add this to the end

    Please Login or Register  to view this content.
    where the bold x is your new row number

    there is probably a better way but this will do it

    see attached

    example data.xlsx

  11. #11
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Look up Partial Cell contence

    oh someone already gave you somthing.

    could you explain how that works please

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Look up Partial Cell contence

    SEARCH($E$2:$E$4,A2) looks for each value in E2:E4 within A2 and creates an array of either the position it's found at or an error
    wrapping that in ISNUMBER converts it to an array of TRUE or FALSE values-true for a match and false for no match
    when dividing 1 by that array excel treats TRUE as 1 and FALSE as 0 so returns an array of either 1/1 or 1/0 - so an array of 1 or DIV/0 errors
    LOOKUP ignores error values so looking up 2 in that array finds the last value that is a 1 and then returns the corresponding value from the F2:F4 range

    simple as my uncle Walt ;-)

  13. #13
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Look up Partial Cell contence

    ps since search returns a number greater than or equal to one or an error the formula can actually be reduced to
    =LOOKUP(2,1/(SEARCH($E$2:$E$4,A2)),$F$2:$F$4)

  14. #14
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Look up Partial Cell contence

    Ok I get it. Thanks for the info. Everydays a schoolday

  15. #15
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Look up Partial Cell contence

    Ah, I see, its the other way around ..
    Based on your example file, here's one way to achieve it

    Put in B2, normal ENTER to confirm will do:
    =INDEX($F$2:$F$4,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($E$2:$E$4,A2)),),0))
    Copy down

    -----
    Above tested fine here. Success? Celebrate it, click the star at the bottom left of my responses

  16. #16
    Registered User
    Join Date
    09-03-2012
    Location
    Newmarket, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Look up Partial Cell contents

    you are all amazing!
    Thank you!

  17. #17
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Look up Partial Cell contents

    @ AlexKen

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, you may not be aware that you can 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.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1