IFS and INDEX/ MATCH EXCEL COUNTERPART IN SQL QUERY

My table looks like this

NAME        BRAND    REFERENCE      **COMMENTS** 
Gu          Skirt    101128           Pants
Cci         Pants    101127           Pants
Cha         Skirt    paired           paired
Gu          Pants    101128           Skirts
Nel         Skirt    nonpaired        UNIQUE
Gir         Pants    101188           Skirt
Baud        Skirt    dropped          DROPPED
Le          Pants    paired           PAIRED 
Gir         Skirt    101188           101178 
Vis         Socks                     blanks
Cci         Skirts   101127           Skirts

I wonder what code to use to get the Comment result. First reference in NUMBERS should be paired.If reference numbers match, return value should be the Brand counterpart. If reference is in Character, they have to fall under if statements: IF character is Nonpaired return value should be unique, dropped for dropped and so on. If the reference is blanks no change.

is this possible? Thank you so much.