MATCH first occurrence search range for string

[eggyjla]

I have a column of alphanumeric values separated by commas (and maybe a comma followed by space) that I'm wishing to return the array position (or row number). Formula input "3" would return 1, input "6" returns 4. The problem I'm having is the formula below is returning 3 for input of 6 rather than 4. Looking to see how to separate and search values individually.


1,3
2,4
5,61
6
2,6,9

{=MATCH("*"&B1&"*",SUBSTITUTE(A1:A4,A1:A4,A1:A4),)}

 

[Marcelo Branco]

Try

=MATCH("*,"&B1&",*",","&SUBSTITUTE(A1:A5," ","")&",",0)
Ctrl+Shift+Enter

M.

 

[James006]

@ Marcelo Branco

Very Smart ... !!!

 

[Marcelo Branco]

@ Marcelo Branco

Very Smart ... !!!

Thank you !

M.