• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What function to use, return value using wild cards

J

Jodi

New Member
Hi, I am not sure what function(s) to use for this or how I would formulate it:

Column A is a list of Styles (WL799, ML895, A123, K4598, etc). In column B, I'd like to return a value of Women if it starts with W (W*), Men if it starts with M (M*) and so on.

I've tried a number of functions, ifs, vlookup, combinations, etc and I still keep getting errors.

Can anyone help me?

Thanks in advance.
 
Thank you, I started at least getting it worked out without returning errors but now it just returns a 0...I'm getting there little by little. But wasn't sure how to add so many variables. You will see what I mean when you view the file. =IF(A2="W*",Women,IF(A2="M*",Men,0))
 

Attachments

Hi,

Use below formula in cell B2

=IF(LEFT(A2,1)="W","Women",IF(AND(LEFT(A2,1)="M",LEFT(A2,1)="A"),"Man",IF(LEFT(A2,1)="N","Unisex",IF(LEFT(A2,1)="N","Unisex",IF(LEFT(A2,1)="K","Kids","")))))

Incase you feel it is very long create a vlookup list
 
Hi,

A small change in my previous formula. Use 'Or' instead of 'And'

=IF(LEFT(A2,1)="W","Women",IF(Or(LEFT(A2,1)="M",LEFT(A2,1)="A"),"Man",IF(LEFT(A2,1)="N","Unisex",IF(LEFT(A2,1)="N","Unisex",IF(LEFT(A2,1)="K","Kids","")))))
 
Hi Shrivallabha,

I checked your formula in the sample file uploaded earlier. Seems to have problem when it comes to Unisex

Please check
 
Elaborating on Sathish KV

I would do it with the following formula
=CHOOSE(MATCH(LEFT(A2;1);{"W";"M";"A";"N";"K"};0);"Woman";"Man";"Man";"Unisex";"Kids")
 
Yes. I did not check all cases which I should've done. LOOKUP is binary search. Better to go with your or GFC's solution (make sure you replace ";" with ",").
 
You must log in or register to reply here.
Back
Top