# Look up Partial Cell contents

1. ## 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. ## 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

3. ## 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. ## 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. ## 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. ## Re: Look up Partial Cell contence

example data.xlsx

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. ## 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.

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. ## 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. ## 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

10. ## 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. ## Re: Look up Partial Cell contence

oh someone already gave you somthing.

could you explain how that works please

12. ## 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. ## 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. ## Re: Look up Partial Cell contence

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

15. ## 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. ## Re: Look up Partial Cell contents

you are all amazing!
Thank you!

17. ## Re: Look up Partial Cell contents

@ AlexKen

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.

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

#### 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