-1

I have two columns in Excel A & B - the values in column B are contained within larger strings in column A.

ie column A: abcedsdes4568sdfs Column B: es456

How do I use countIF to show/detect any matches?

Tony
  • 8,681
  • 7
  • 36
  • 55
  • Possible duplicate of [Excel COUNTIF cell contains a given text (partial match)](https://stackoverflow.com/questions/32441310/excel-countif-cell-contains-a-given-text-partial-match) – LondonRob Jul 20 '17 at 15:20

1 Answers1

6

If you just want to know whether the B value has a [partial] match with column A try using wildcards, e.g. this formula in C1 copied down

=COUNTIF(A:A,"*"&B1&"*")>0

TRUE means a match

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • `=COUNTIF(A:A,"*"&B1&"*")` works - now is there a way to highlight or color the A:A cell that matched? – Tony Feb 02 '15 at 17:13
  • If you have many column B items how would you want that to work - you could highlight all column A cells which match......but then you wouldn't know which matches with which? – barry houdini Feb 02 '15 at 17:19
  • Thanks - I don't really care which of column B matched with which of column A just that there was a match. So yes simply highlighting the cell in column A would be good. That way I'm left with column A where highlighted = match and non-match = no highlight – Tony Feb 02 '15 at 17:21
  • OK, assuming you have column B data in B1:B10 (change as required) then you can select column A and apply conditional formatting using this formula: `=COUNT(SEARCH(B$1:B$10,A1))` – barry houdini Feb 02 '15 at 18:38
  • So do I still do the countIF which ends up in column C and then the count is based on that column or countIF is no longer needed and just use the COUNT? – Tony Feb 02 '15 at 18:43
  • The two don't depend on each other - the formula for column C is a worksheet formula - I'm suggesting that you use this second formula in conditional formatting (not on the worksheet) - do you know how to use conditional formatting? – barry houdini Feb 02 '15 at 18:52
  • I think the problem with the conditional formula is that its not doing the partial match as the workbook formula does? ie something like =COUNT(SEARCH(""B$1:B$10 "",A1)) but that doesn't seem to do anything either and for the record my data that needs the wildcard is in column 'I' and the data we are searching against is column 'A' – Tony Feb 02 '15 at 19:42