Find text within text and then return that text

[Brendadob]

I have a spreadsheet that contains a style number that I need to lookup in a column of text fields, when it finds that style number I need it to return the entire text string in the description field.
Column A has the text I am searching for in Column J, when it finds it I need the entire field of J to appear in Column B.
Thank you in advance for your help.

[leelnich]

Hi Brendadob- paste this ARRAY FORMULA in B2, then press CTRL+SHIFT+ENTER to confirm:
=INDEX(LEFT($J$2:$J$270,LEN($J$2:$J$270)-LEN(A2)-3),MATCH(" - " & A2,RIGHT($J$2:$J$270,LEN(A2)+3),0),1)
Formula updated 7:17 pm-LLN

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[Brendadob]

Thank you so much for your reply. Unfortunately I get #N/A when I copy the formula in.

[Brendadob]

Also, I'm okay if it enters the line as is, there is no need to take the style number out of Column J when placing in Column B.

[leelnich]

Just updated post #2. If you've already downloaded, Re-Do.

[Brendadob]

2nd Edit worked on some but not all

[Phuocam]

Or try this ...

=LOOKUP(2,1/(RIGHT($J$2:$J$270,LEN(A2)+2)="- "&A2),$J$2:$J$270)

[leelnich]

Here's an updated version.
=INDEX($J$2:$J$270,MATCH(" - " & A2,RIGHT($J$2:$J$270,LEN(A2)+3),0),1)

The #NA means you didn't confirm the ARRAY FORMULA correctly. Follow these steps:
1) Select B2
2) Press the F2 key. This puts you in edit mode, with the cursor in the formula bar.
3) Paste the formula. DO NOT PRESS ENTER...
4) Instead, press CTRL+SHIFT+ENTER, all at once. You should see curly brackets {} appear around the formula, indicating it's ARRAY status.
5) Press CTRL+C to copy cell B2
6) Select B3:B270 and paste. You should now see the correct results.

NOTE- With Array formulas, the copied cell CANNOT be part of the PASTE area. This differs from normal copy/paste procedures.

ps.: Don't forget to turn Automatic calculation back on. I wasted 15 minutes trying to figure out why the original formula wasn't working before I thought to check.

[Brendadob]

No, I'm an idiot. Because my file was so big, I only uploaded a small portion of it. All done! Thank you so much!

[leelnich]

Happy to help, Thank you for the rep!