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]

It would greatly help if you used XL2BB to show your sample data and expected result(s).

 

[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

It would greatly help if you used XL2BB to show your sample data and expected result(s).

Match Result.xlsx
	B	C	D
1	Non-Sequential Values	Values	Matched Result
2	740023485209	026664278135, 786714215335, 884984185218, 884949627647	026664278135
3	029695323003	716184544086, 089238300212, 342192107809, 342192107748, 892383002128, 611102105656, 015568971242, 781163724247	892383002128
4	029695323010	660048001959, 622013297958, 078433272189, 885137765271, 626211276968, 653801183535, 799360746849	653801183535
5	026664278135	622013297965, 069408137190, 740023485209, 660048001966, 626211276951	740023485209
6	653801183535	029695323010	029695323010
7	892383002128	029695323003, 786714526790	029695323003
Sheet1

 

[kweaver]

Are your instruction in reverse? Column C has numbers separated by a comma. Column B has single numbers.
What determines the results in column D?

 

[abdulrahim845]

Are your instruction in reverse? Column C has numbers separated by a comma. Column B has single numbers.
What determines the results in column D?

C2 has multiple numbers, from these numbers 1 number must be matched from B2:B7. I want to match that matching number and get output in D2

 

[abdulrahim845]

Are your instruction in reverse? Column C has numbers separated by a comma. Column B has single numbers.
What determines the results in column

Hi

kweaver, did you understand my query?​

 

[kweaver]

Maybe this?

Sub VinC()
Dim ary() As String, i As Long, lr As Long, u As Long, w As Variant
lr = Cells(Rows.Count, "B").End(xlUp).Row

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

I put my results in the E column just to compare. Change accordingly

 

[abdulrahim845]

Thanks for the help. Execute the first time now It gets stuck at the "Else:" argument.

 

[kweaver]

Looks like you put a stop in the code.

Book1
	B	C	D	E
1	Non-Sequential Values	Values	Matched Result
2	740023485209	026664278135, 786714215335, 884984185218, 884949627647	026664278135	026664278135
3	029695323003	716184544086, 089238300212, 342192107809, 342192107748, 892383002128, 611102105656, 015568971242, 781163724247	892383002128	892383002128
4	029695323010	660048001959, 622013297958, 078433272189, 885137765271, 626211276968, 653801183535, 799360746849	653801183535	653801183535
5	026664278135	622013297965, 069408137190, 740023485209, 660048001966, 626211276951	740023485209	740023485209
6	653801183535	029695323010	029695323010	029695323010
7	892383002128	029695323003, 786714526790	029695323003	029695323003
Sheet1

 

[abdulrahim845]

I am using VBA for the first time. Maybe I did some mistake. Now I re-apply and it runs smoothly, but it didn't get all values. What may the error be?

Match Result.xlsm
	B	C	D	E
1	Non-Sequential Values	Values	Matched Result
2	740023485209	026664278135, 786714215335, 884984185218, 884949627647	026664278135	26664278135
3	029695323003	716184544086, 089238300212, 342192107809, 342192107748, 892383002128, 611102105656, 015568971242, 781163724247	892383002128
4	029695323010	660048001959, 622013297958, 078433272189, 885137765271, 626211276968, 653801183535, 799360746849	653801183535
5	026664278135	622013297965, 069408137190, 740023485209, 660048001966, 626211276951	740023485209
6	653801183535	029695323010	029695323010	29695323010
7	892383002128	029695323003, 786714526790	029695323003	29695323003
Sheet1