Condition formulas from text list to table

trekker1218

Board Regular
Joined
Feb 15, 2018
Messages
86
Office Version
  1. 2019
Platform
  1. Windows
Hello,
I have a list of text data in cell AQ2:AQ430
i need to find every occurrence of that list across cell M2:AP500
Then Highlight the matches only.
I have gone through plenty of permutations of the condition formulas most have suggested with no good results.
tried index
countif
isna
vlookup

formulas and cant get it to what I want. Some formulas just highlight ALL items, some highlight some but not all items in AQ2:AQ430

any help would be appreciated.
A VBA would be fine if it works too.

thank you all again.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try this:
1. Select your range M2:AP500
2. Go to Conditional Formatting, select the Formula option, and enter this formula:
Code:
=COUNTIF($AQ$2:$AQ$430,M2)>0
3. Select your desired highlight color
4. Click OK

All values in M2:AP500 that are in the list AQ2:AQ430 should now be highlighted.
 
Last edited:
Upvote 0
Just another way:

Enter this formula in CF

=MATCH(M2,$AQ$2:$AQ$430,0)

Applies to:

=$M$2:$AP$500
 
Upvote 0
Thank you. I had that same formula and missed the >0 part.

thank you.
 
Upvote 0
It also reviews the absolute reference:
$AQ$430
 
Upvote 0
It also reviews the absolute reference:
$AQ$430
Good catch, Dante.
I went back and updated the formula.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top