Conditional Formatting, match a list to multiple columns

ExcelNightmare

New Member
Joined
Jul 19, 2017
Messages
2
I have a list of values on another sheet that I want to try and match to two columns (there are multiple lists to match to different sets of columns so something easy to repeat would be great). If one of the values in the given list (changeable) matches a value in the columns, the whole row would be highlighted. The list values can match either one of the two columns or both.

After all the matches are highlighted, I want to count the amount of rows highlighted.

So far I have used:

=MATCH($P1,Search!$B$4:$B$40,0) #to find values in the column to match the list but I had to repeat it multiple times as it seems MATCH does not handle multiple columns
=MATCH($R1,Search!$B$4:$B$40,0)
Is there a way to put them together? I have over 30 conditional formatting now....

Also I have used Filter and "=SUBTOTAL(102,Data!I:I)" to count the number of rows highlighted, is there a better way?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,141,049
Messages
5,703,932
Members
421,321
Latest member
blusky4

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top