title: How to search for a number in a column with comma separated values in Excel
body: <p><a href="https://i.stack.imgur.com/wHXav.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wHXav.png" alt="Screenshot" /></a></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Column A</th>
  <th>Column B</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>4</td>
  <td></td>
  </tr>
  <tr>
  <td>5</td>
  <td>1,4</td>
  </tr>
  <tr>
  <td>etc</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Trying to figure out how to search if a Number in Column A exists anywhere in Column B.</p>
  <p>So far I've tried:</p>
  <pre><code>=IF(ISNUMBER(SEARCH(A2,B:B)),&quot;YES&quot;,&quot;NO&quot;) which returns #spill 
  </code></pre>
  <p><a href="https://stackoverflow.com/questions/45024463/excel-function-or-vba-code-to-search-for-number-in-comma-separated-string">This UDF</a>:</p>
  <pre><code>Function CheckOne(rng As Range, chkValue As Long) As Boolean
      Dim n
      For Each n In Split(rng.Value, &quot;,&quot;)
          If CLng(n) = chkValue Then
              CheckOne = True
              Exit For
          End If
      Next n
  End Function
  </code></pre>
  <p><a href="https://stackoverflow.com/questions/25736873/searching-comma-separated-numbers-within-a-cell">Another UDF:</a></p>
  <pre><code>Function CommaSeparatedListContains(ByVal csv As String, ByVal v As String, _
          Optional ByVal delimiter As String = &quot;,&quot;) As Boolean
      Dim i As Long
      Dim splitCsv() As String
      splitCsv = Split(csv, delimiter)
      CommaSeparatedListContains = False
      For i = LBound(splitCsv) To UBound(splitCsv)
          If splitCsv(i) = v Then
              CommaSeparatedListContains = True
              Exit Function
          End If
      Next i
  End Function
  </code></pre>
  <p>This didn't work either as I'm getting #VALUE! error</p>
  <p>I suspect it's because the value I am looking for occurs more than once in column B.</p>
  <p>What am I doing wrong here?</p>