Search Using an Array Formula...

Evening,

i am currently using the following array formula to search a range for a value and return its self refrence however i need to modify it as at present it will only return exact matches i need it to be able to search a string find a value and return the cell refrence

{=IF(E2=0,"-",ADDRESS(MAX((A1:A500=E2)*ROW(A1:A500)),MAX((A1:A500=E2)*COLUMN(A1:A500)),4))}

e.g

if A:A contains product codes like below

41_74abdc
41_74defg
41_74hijk

and the user searches for "defg" i will need the formula to return A2

any insight would be appreciated

there is no worry of duplicates as they cannot occur...

=ADDRESS(MATCH("*"&B1,A:A,0),1) where b1 contains defg

you can just alter Martin's original and add a 2nd wildcard post partial string, ie:

=ADDRESS(MATCH("*"&B1&"*",A:A,0),1)