• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can I use VLOOKUP to set conditional formatting?

G

grumpus

New Member
Suppose I have a name in B2 and then a series of numbers in C2:M2. I have a set of data (numerical) in cells C1:C100 (some of these numbers may be duplicates). What I would like to do is set a conditional format (red) on the cells in C2:M2 should they match any of the numbers in the cells A1:A100.


Finally, I would like to have a count in N2 of the total highlighted cells in C2:M2. I’m using Excel 2007.
 
Hi Grumpus,


First of all welcome to Chandoo.org !!


Will it be possible for you to post a sample dataset ?


Cheers,


Z.
 
Conditional format for C2:M2, with C2 being the active cell:


=ISNUMBER(MATCH(C2,$C$1:$C$100,0))

(Btw, these two ranges appear to intersect...may need to adjust


Count of cells:

=SUMPRODUCT(1*(ISNUMBER(MATCH(C2:M2,C1:C100,0))))
 
Perfect, thanks Luke...yes, I did make the numbers intersect in error...I rectified it in my original post but this solution works well. Thanks again!
 
You must log in or register to reply here.
Back
Top