Forum Discussion
Excel search
Hello,
in the following screenshot, I moved the list to column F and created a helper column in column D with the formula
=IF(COUNTIF(F:F,A2)>0,TRUE,FALSE)
in cell D2 and copied down.
Then I selected the whole table from row 2 to the bottom and created a new conditional formatting rule with the formula
=$D2
I selected a yellow fill and confirmed all dialogs.
If you have the list of 2000 names somewhere in the workbook, you can add a helper column to the original data table with a formula along the lines of
=if(countif(Namelist!$A:$A,[@Name])>0,True,false)
So, look at the name in the current table and find it in the list on the other sheet. Return TRUE if found and FALSE if not.
Now you can use conditional formatting on your table, using a formula that looks at the value in the helper column. Select the table and, assuming the current cell is in row 2 the Helper column is in column Z, use a formula like this
=$Z2
The value of Z2 is either TRUE of FALSE, so you don't need a complex formula. Just make sure that the column reference has a $ sign and the row number has NO $ sign in front of it. Then select a fill color to highlight and apply the rule to your whole table.
With this approach, you can add more names to your list of 2000 and they will automatically be highlighted in the table.
Let me know if that works for you.