Format Cell if value found in another column

English
, , ,
#1

Hello,
(edit : sorry, I mixed French and English. SOMMEPROD is French, so it should be SUMPRODUCT instead)
I have the following table :
Table

I want to format cell in column A if their value is found in column C (without looking in column B)

I end up with that formulas but it does look in column A, B, C and I did not find a way to look in a single column : IF(ISBLANK(A1);"";SUMPRODUCT($A$1:$C$6=A1)*1>1) ; And Julien, Bruno, Julien, Martin, Pierre are formated.

But I just want to target and format Bruno and Martin, as these 2 only are in column C. Julien (in the same column) and Pierre (in the column B) should not be targeted.

I tried to replace $A$1 by $C$1 but no luck : IF(ISBLANK(A1);"";SUMPRODUCT($C$1:$C$6=A1)*1>1)

#3

Mixing English and French function names does not promote the functionality of the formula either. :wink:

#4

Conditional format for A1:A6
Formula is: ISNUMBER(MATCH($A1;$C$1:$C$6;0))

#5

Yeah sorry! I complete forgot to translate that one. I’ll correct it in my post

#7

I just find out that my doc data is not consistent. Sometimes, the first letter is uppercased and sometimes, not.

I tried to add LOWER() in the formulas (in French MINUSCULE() ) but it does not work and “broke” the formulas actually.

IF(ISBLANK(A1);"";SUMPRODUCT(C$1:C$6=MINUSCULE(A1)))

#2

Try: SOMMEPROD(C$1:C$6=A1)

1 Like
#6

Thanks you, it worked.

IF(ISBLANK(A1);"";SUMPRODUCT(C$1:C$6=A1))

Or in French :stuck_out_tongue:
SI(ESTVIDE(A1);"";SOMMEPROD(C$1:C$6=A1))

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.