Conditional formatting using SUMPRODUCT. Match two lists

[srizki]

Hi All,
I need to match two lists and find the matched value. The short list has 16 digits invoice number, while the lookup table has long list with the same invoice numbers but with the extention “-SBOO2”, “-SBOO3”,
I need to match the invoice number. I am using the conditional formatting with the following formula.
=SUMPRODUCT(--ISNUMBER(SEARCH(A4"*",$F$4:$F$10)))
Where Column A has the long list. I highlighted the long list.

Please advise.

 

[Jeffrey Mahoney]

I guess I'm confused. Conditional formatting doesn't need to search for a matching value. You simply provide the range you want to affect and provide a formula that will result in TRUE or FALSE. I tried to use your formula and got random results. The formula below is how my brain works.

=IF(IFERROR(MATCH(A4,$F$4:$F$10,FALSE),0)+IFERROR(MATCH(A4&"-SB0002",$F$4:$F$10,FALSE),0)+IFERROR(MATCH(A4&"-SB0003",$F$4:$F$10,FALSE),0)>0,TRUE,FALSE)