Lookup and match multiple values separated by commas in a cell and produce matched value

[abdulrahim845]

C2:C7 has single non-sequential numbers and B2:B7 has multiple numbers separated by commas in each cell. I want to match the C2 numbers in B2:B7 and get the output of the corresponding matching number in D2. Can anyone help me formulate this? Thanks

 

[kweaver]

Is it possible that more than 1 value will be found? I adjusted my VBA version:

Sub FindC_in_B()
Dim ary() As String, i As Long, lrb As Long, lrc As Long, u As Long, f As String
lrb = Cells(Rows.Count, "B").End(xlUp).Row
lrc = Cells(Rows.Count, "C").End(xlUp).Row

For i = 2 To lrc
f = ""
 ary() = Split(Cells(i, "C"), ", ")
  For u = LBound(ary) To UBound(ary)
   If IsNumeric(Application.Match(ary(u), Range("B2:B" & lrb), 0)) Then
   f = f & Right("0" & ary(u), 12) & ", "
   ' Cells(i, "E") = Right("0" & ary(u), 12)
    Else
   End If
  Next u
  Cells(i, "E") = Left(f, Len(f) - 2)
found: Next i
End Sub

 

[abdulrahim845]

I am sorry to say, I did all as you said, but it's not working. May be some error on my end.

 

[kweaver]

I am sorry to say, I did all as you said, but it's not working. May be some error on my end.

Did you place the macro in a MODULE for that workbook?
How is it NOT working?

 

[abdulrahim845]

Is it possible that more than 1 value will be found? I adjusted my VBA version:

Sub FindC_in_B()
Dim ary() As String, i As Long, lrb As Long, lrc As Long, u As Long, f As String
lrb = Cells(Rows.Count, "B").End(xlUp).Row
lrc = Cells(Rows.Count, "C").End(xlUp).Row

For i = 2 To lrc
f = ""
 ary() = Split(Cells(i, "C"), ", ")
  For u = LBound(ary) To UBound(ary)
   If IsNumeric(Application.Match(ary(u), Range("B2:B" & lrb), 0)) Then
   f = f & Right("0" & ary(u), 12) & ", "
   ' Cells(i, "E") = Right("0" & ary(u), 12)
    Else
   End If
  Next u
  Cells(i, "E") = Left(f, Len(f) - 2)
found: Next i
End Sub

Yes, there are some chances that we may encounter more than 1 matching value. I was about to ask, but I restained myself, thought its enough help. Anyways Thanks @kweaver

 

[awoohaw]

why haven't you tried the formula in post #20?

 

[abdulrahim845]

Did you place the macro in a MODULE for that workbook?
How is it NOT working?

I was giving remarks about this formula. Your macro works fine. I am learning excel, for my personal work to automate. It is a great opportunity to solve and learn same problem with different solution sets

=IFERROR(IF(ISNUMBER(FIND(",",$B1,1))=FALSE,INDEX($A$1:$A$2000,MATCH(B1,$A$1:$A$2000,0)),MID($B1,SUM(IFERROR(FIND($A$1:$A$2000,B1),0)),FIND(",",$B1,1+SUM(IFERROR(FIND($A$1:$A$2000,B1),0)))-SUM(IFERROR(FIND($A$1:$A$2000,B1),0)))),"")

 

[abdulrahim845]

why haven't you tried the formula in post #20?

I tried, but facing issues as it didn't return all values, even I applied by CTRL_SHT_ENTER. Can I share the actual sheet here?

 

[kweaver]

I made one other modification in case NONE of the values are found:

Sub FindC_in_B()
Dim ary() As String, i As Long, lrb As Long, lrc As Long, u As Long, f As String
lrb = Cells(Rows.Count, "B").End(xlUp).Row
lrc = Cells(Rows.Count, "C").End(xlUp).Row
Range("E2:E" & lrc).NumberFormat = "@"

For i = 2 To lrc
f = ""
 ary() = Split(Cells(i, "C"), ", ")
  For u = LBound(ary) To UBound(ary)
   If IsNumeric(Application.Match(ary(u), Range("B2:B" & lrb), 0)) Then
   f = f & Right("0" & ary(u), 12) & ", "
   ' Cells(i, "E") = Right("0" & ary(u), 12)
    Else
   End If
  Next u
  Cells(i, "E") = Left(f, Application.Max(0, Len(f) - 2))
  
found: Next i
End Sub

 

[abdulrahim845]

I made one other modification in case NONE of the values are found:

Sub FindC_in_B()
Dim ary() As String, i As Long, lrb As Long, lrc As Long, u As Long, f As String
lrb = Cells(Rows.Count, "B").End(xlUp).Row
lrc = Cells(Rows.Count, "C").End(xlUp).Row
Range("E2:E" & lrc).NumberFormat = "@"

For i = 2 To lrc
f = ""
 ary() = Split(Cells(i, "C"), ", ")
  For u = LBound(ary) To UBound(ary)
   If IsNumeric(Application.Match(ary(u), Range("B2:B" & lrb), 0)) Then
   f = f & Right("0" & ary(u), 12) & ", "
   ' Cells(i, "E") = Right("0" & ary(u), 12)
    Else
   End If
  Next u
  Cells(i, "E") = Left(f, Application.Max(0, Len(f) - 2))
 
found: Next i
End Sub

Matching, more than 1 matching value and None can be done by this single macro.?

 

[kweaver]

Matching, more than 1 matching value and None can be done by this single macro.?

yes.