View original (French)

Test if value in a range

Solved
xuorel Posted messages 253 Registration date   Status Member Last intervention   -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello,

I would like to perform the following operation in Excel (without using VBA):

- I have values in the range B1:B10
- I want to know for each of these values if it is found in the range C1:C20.
- This can be shown either by placing a checkmark in the adjacent cells in A1:A10 or by coloring the cells in B1:B10.

Thank you for your help.

Configuration: Windows / Internet Explorer 11.0

1 answer

  1.
    mrjenkins Posted messages 611 Status Member 103
     
    Hi,

    you can put in A1 (then drag down): 
    =IF(ISERROR(MATCH(B1,$C$1:$C$20,0)),"","x")


    a+
    0
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Hello
      to keep it simple, to pull down:
      =IF(COUNTIF($C$1:$C$20,B1),"true","")

      or conditional formatting by formula
      select B1:B10
      conditional formatting "use a formula"
      =COUNTIF($C$1:$C$20,B1)
      Format and ok: ok
      best regards
      1