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

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:

Formula:

Please Login or Register  to view this content.

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!

Thanks Daffodil. Solution works perfectly.