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

 

[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

It only returns values, starting from "0"

 

[awoohaw]

I don't know why you posted the same question twice.

mr excel questions 14.xlsm
	A	B	C	D	E
1	740023485209	026664278135, 786714215335, 884984185218, 884949627647	026664278135	026664278135
2	029695323003	716184544086, 089238300212, 342192107809, 342192107748, 892383002128, 611102105656, 015568971242, 781163724247	892383002128	892383002128
3	029695323010	660048001959, 622013297958, 078433272189, 885137765271, 626211276968, 653801183535, 799360746849	653801183535	653801183535
4	026664278135	622013297965, 069408137190, 740023485209, 660048001966, 626211276951	740023485209	740023485209
5	653801183535	029695323010	029695323010	029695323010
6	892383002128	029695323003, 786714526790	029695323003	029695323003
7
Sheet8

Cell Formulas
Range	Formula
D1:D6	D1	=IFERROR(IF(ISNUMBER(FIND(",",$B1,1))=FALSE, INDEX($A$1:$A$6,MATCH(B1,$A$1:$A$6,0)), MID($B1,SUM(IFERROR(FIND($A$1:$A$6,B1),0)), FIND(",",$B1,1+SUM(IFERROR(FIND($A$1:$A$6,B1),0)))- SUM(IFERROR(FIND($A$1:$A$6,B1),0)))),"")

 

[abdulrahim845]

Hi, I just joined the platform. I posted the same question twice by mistake. 2nd it's only returning the value in D5.

 

[abdulrahim845]

It's now working like a charm. Thanks for the great help.

 

[awoohaw]

did you have to adjst the formula or anything? as you can see it is working in all cells it is copied into.

 

[abdulrahim845]

did you have to adjst the formula or anything? as you can see it is working in all cells it is copied into.

I made the same scenario as you did. Column A with A1 and so on. Then paste the formula, returning the value in D5 only

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

Cell Formulas
Range	Formula
D1:D6	D1	=IFERROR(IF(ISNUMBER(FIND(",",$B1,1))=FALSE,INDEX($A$1:$A$6,MATCH(B1,$A$1:$A$6,0)),MID($B1,SUM(IFERROR(FIND($A$1:$A$6,B1),0)),FIND(",",$B1,1+SUM(IFERROR(FIND($A$1:$A$6,B1),0)))-SUM(IFERROR(FIND($A$1:$A$6,B1),0)))),"")

 

[awoohaw]

OKAY, instead of pressing the ENTER key to enter the formula, use this keyboard combination: CNTL-SHIFT-ENTER

 

[abdulrahim845]

For the current data set it's working fine, but when I apply the same on a similar large data set having A1:A1301 its returning missing values

 

[awoohaw]

For the current data set it's working fine, but when I apply the same on a similar large data set having A1:A1301 its returning missing values

you need to change all the 6's in the formula to 1301 (but I suggest 2000 if you are going to be adding many more each month.)

 

[awoohaw]

=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)))),"")

Remember to enter the formula as CTRL-SHIFT-ENTER