Compare a range

[cyliyu]

I used the ISNA & MATCH to check a range from D1:K30 but not working.

=IF(J11="","",IF(ISNA(MATCH(A1,$D$1:$K$30,0)),"","CAR"))

I have to repeat the formula from column D to K as follow.

=IF(A1="","",IF(ISNA(MATCH(A1,$D$1:$D$30,0)),IF(ISNA(MATCH(A1,$E$1:$E$30,0)),IF(ISNA(MATCH(A1,$F$1:$F$30,0)),IF(ISNA(MATCH(A1,$G$1:$G$30,0)),IF(ISNA(MATCH(A1,$H$1:$H$30,0)),IF(ISNA(MATCH(A1,$I$1:$I$30,0)),IF(ISNA(MATCH(A1,$J$1:$J$30,0)),IF(ISNA(MATCH(A1,$K$1:$K$30,0)),"","CAR"),"CAR"),"CAR"),"CAR"),"CAR"),"CAR"),"CAR"),"CAR"),"CAR"))

How to simplify?

 

[bosco_yip]

Try this one :

=IF(J11="","",IF(COUNTIF($D$1:$K$30,A1),"CAR",""))

Regards
Bosco

 

[cyliyu]

Thanks Bosco. It works.