Copying value in A1 based on partial text in H1

Try this:

=IF(ISERROR(FIND("XXX",H2,1)>0),"",FIND("XXX",H2,1))

hi Groovicles. few questions:
1. are you looking for "XXX" or "XXX&"? the latter is what you put in your formula
2. is lower case acceptable? "xxx"? FIND is case sensitive. if you don't mind upper/lower case, use SEARCH
3. if "XXX" cannot be found, your formula will immediately hit an error. so you have to use ISNUMBER to wrap around FIND or use COUNTIF with wildcards

so maybe:
=IF(ISNUMBER(FIND("XXX&",'CORE Data Quarter'!H2)),'CORE Data Quarter'!A2,"")
or:
=IF(COUNTIF('CORE Data Quarter'!H2,"*XXX&*"),'CORE Data Quarter'!A2,"")

adjust the formula accordingly based on my questions. you can remove "&", use SEARCH

Nevermind Ben, got it working thanks to your formula and a little tweak. You're awesome!!

Hi Ben,

Thanks for your reply. To answer you questions:

1. XXX is part of the text string in the cell. The full string would look something like XXX123, XXX345, XXX456. Not all cells in column H have an XXX entry, so I'm just looking for column A data to be copied if there is any XXX data in column H cells.
2. All data is upper case

I tried both your formulas and get blank results (not error, just blank). Thoughts?

Thanks!

Suggest you upload a sample spreadsheet for analysis.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic