Using a Vlookup with Omitted Text Strings

[sccrbrg]

Okay, I've run into a dilemma that I'm hoping someone here can help me solve.

I have a table with a first column that looks something like this:

"Shirt,Sleeveless,Orange,Cotton"
"Shirt,Sleeveless,Green,Cotton"
"Shorts,Sleeveless,Orange,Cotton"
"Pants,Sleeveless,Orange,Cotton"
"Shorts,Sleeveless,Orange,(Premium),Cotton"

The adjacent columns have data that corresponds to each product category. What I need to be able to do is create a generic formula that will return a vlookup value any time that a premium shows up in a product description. For reasons that are not worth going into, the "(Premium)" shows up in different locations within the text string and cannot be standardized.

Here's the kicker - Every time "(Premium)" shows up, I need the vlookup to "look for" the same product description that excludes the premium and return the cost from an adjacent column.

I.E. In this example it would return the cost value for "Shorts,Sleeveless,Orange,Cotton" for "Shorts,Sleeveless,Orange,(Premium),Cotton" - I hate to be inflexible, but this is part of a massive algorithm so it pretty much needs to function exactly as I described it.

I know I can probably run an IF function in the adjacent column and run a If(find ("(Premium,A5),A5,"") and then do a find and replace and find a way to run the lookup off of that - but there are already so many steps involved in running the process that I would like it to run itself automatically.

Any help would be greatly appreciated.

[Cerbera]

Hi there,

Assuming the data is in cell A1 down you can use this formula:

=IF(NOT(ISNUMBER(SEARCH("(Premium)",A1))),"NOT PREMIUM",IF(SEARCH("(Premium)",A1)>1,SUBSTITUTE(A1,",(Premium)",""),SUBSTITUTE(A1,"(Premium),","")))

It handles "removing" "(Premium)" and a leading OR trailing "," depending where the string appears.

I've not put the vlookup part in without knowing what you'll be doing exactly, i.e. no sample sheet. Basically though if your vlookup was to return the second value in a table of G1:H3 it would be:

=IF(NOT(ISNUMBER(SEARCH("(Premium)",A1))),"NOT PREMIUM",VLOOKUP(IF(SEARCH("(Premium)",A1)>1,SUBSTITUTE(A1,",(Premium)",""),SUBSTITUTE(A1,"(Premium),","")),G1:H3,2,0))

Hope that this helps