VBA / Macro help needed for matching partial text string

Folks,

I need a macro to give returs in Column F as "Completed" if Column A and E cell values partial text string is matching. I have attached the excel file of what I'm trying to accomplish.

Thanks in advance for your expertise!

You E2 value looks like you really just want to strip out the AGQ leader and test the rest to see if it exists in A2? IF so, put this formula in F2, then copy down:

=IF(ISNUMBER(SEARCH(SUBSTITUTE(E2, "AGQ", ""), A2)), "Completed", "")

Hi Jerry,

Thank you so much its working perfect. One more help please, if I add new values in Column A and E then I need the formula in F Column get fill down automatically. Is it possible?

Thanks once again.

Joshi

1) Just put the formula in that column now and copy it down as far as you want so it's already there? In that case you might want to change it to:

=IFERROR(IF(ISNUMBER(SEARCH(SUBSTITUTE(E2, "AGQ", ""), A2)), "Completed", ""), "")



2) Highlight your table in A:F, then press CTRL -L to convert it to an Excel LIST (or Table).

Video example

Hi Jerey,
Sorry I forgot to tell you, Some times the row value may vary. Hence we need a Vlookup to look all Column A cell values (i.e if E2 value matching with A4 then F2 returns should be "Completed"). Sorry I should tell this in the first post. Can you please advise whether this can be done.

Thanks,
joshi

Change the F2 formula to:

=IFERROR(IF(ISNUMBER(MATCH("*"&SUBSTITUTE(E2, "AGQ", "")&"*",A:A, 0)), "Completed", ""), "")

Thank you so much Jerry,

Its amazing.

Joshi