# Conditional formatting using SUMPRODUCT. Match two lists

#### srizki

##### Well-known Member
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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)

Replies
5
Views
220
Replies
5
Views
216
Replies
18
Views
494
Replies
5
Views
223
Replies
8
Views
232

1,203,565
Messages
6,056,105
Members
444,846
Latest member
pbailey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back