Conditional formatting with wildcard match

[cwhite86]

I am trying to generate a cross reference highlight format.

I am using this Conditional Format Formula to check and highlight values list in B2:K100 not found in Column M.

B2:K100 Has values something like this

10001 10005
10002 10006

Column M has data inserted from another program and looks like this

9JFK20002 1" 6C LMNOP RIP 10002 644.000


I am using this Conditional Format Formula to check and highlight values list in B2:K100 not found in Column M.

=ISNA(MATCH("*"&B2&"*",$M$1:$M$500,0))

This works wonderfully. What I need now is a cross reference in Column M. I need to look in column M specifically for the 10002 value in B2:K100. If no match is found, then highlight.

I would assume all I need to do is switch the "*" wildcard function up a bit, but I am not sure exactly how.

 

[navic]

What I need now is a cross reference in Column M. I need to look in column M specifically for the 10002 value in B2:K100. If no match is found, then highlight.

If I understand you well you want to highlight a cell in column M that contain a specific number as a condition.
Try this formula applied in the Conditional Formatting on the range in column M. Select range M5 to last row data

=IF(IFERROR(VALUE(LEFT(RIGHT($M5,LEN($M5)-FIND(TEXT($U$1,0),$M5)+1),5))=$U$1,"")=TRUE,TRUE,FALSE)

In cell U1 is a condition
See Attachment

 

[Beamer]

Hi, I read your question a little differently to navic. navic's formula is perfect if you do want to just match that one number, but I got the feeling that you wanted each cell in column M to check if it has a matching number in B2:K100.
If that's true, then I think this will work (although I don't know what will happen with different dollar amounts).

=AND(COUNTIF($B$2:$K$100,LEFT(RIGHT(M1,21),5))=0,ISNUMBER(VALUE(LEFT(RIGHT(M1,21),5))),LEN(M1)>20)

 

[cwhite86]

Thanks for your help both of you. This is what I ended up with.

=NOT(SUMPRODUCT(COUNTIF(A1,"*"&BI2C!$B$2:$K$100&"*"), --(BI2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&BW1C!$B$2:$K$100&"*"), --(BW1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&BW2C!$B$2:$K$100&"*"), --(BW2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&CHY1C!$B$2:$K$100&"*"), --(CHY1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&CHY2C!$B$2:$K$100&"*"), --(CHY2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&HK1C!$B$2:$K$100&"*"), --(HK1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&HK2C!$B$2:$K$100&"*"), --(HK2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&HKBWP!$B$2:$K$100&"*"), --(HKBWP!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&HM1C!$B$2:$K$100&"*"), --(HM1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&HM2C!$B$2:$K$100&"*"), --(HM2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&RO1C!$B$2:$K$100&"*"), --(RO1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&RO2C!$B$2:$K$100&"*"), --(RO2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&RQWO1C!$B$2:$K$100&"*"), --(RQWO1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&RQWO2C!$B$2:$K$100&"*"), --(RQWO2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&WO1C!$B$2:$K$100&"*"), --(WO1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&WO2C!$B$2:$K$100&"*"), --(WO2C!$B$2:$K$100<>"")))*(A1<>"")

This is because I needed to search multiple columns on multiple pages also. While lengthy this did the trick.