match() an array filtered based on criteria in another column

[simarui]

i've got a list of product codes in column A for which i'm trying to return a high level product family from column B. many of the values in product family are a generic term, not specific to the actual product, so I'm trying to look through my dataset for similar product codes and return a product-specific family instead of the generic term.

In the sample attached, abcdefg is a product code which is followed by either 10 or 01. Product Family can be either the generic terms "A" or "B", or the product-specific term "C". In my example formula in c2, I want to return "C" - the first non-generic value for which i can match the product code abcdefg.

I'm thinking the formula should look something like:

{=INDEX($A$3:$B$7,MATCH(LEFT(A2,LEN(A2)-2),--(NOT(OR(B3:$B$7="B",B3:$B$7="A")))*left(A3:$A$7,len(A3:$A$7)-2),0),2)}

but Not()*A5:$A$7 returns a value error - multiplying "True" by my array... what i think i want is to return {false,true,true,false,false,true} so that my match formula is only looking at the array {B3,B4,0,0,B7} to find "abcdefg".

open to pretty much any suggestions including helper columns etc...

thanks in advance!

[daffodil11]

Good ol' fashioed LOOKUP to the rescue.

=LOOKUP(2,1/((LEFT($A$2:$A$7,LEN($A$2:$A$7)-2)=LEFT(A2,LEN(A2)-2))*
             ($B$2:$B$7<>"A")*
             ($B$2:$B$7<>"B")),$B$2:$B$7)

[simarui]

Thanks Daffodil. Solution works perfectly.