Index Match Formula in Excel

[sshutchinson]

Hello all,

I have an Index Match formula that is using Text to Search column. I have gotten this to work in Excel and now trying to build an Alteryx workflow to produce similar results. 

To make this easy to understand I included the Text to Search column, the Ref Key column, and OUTPUT column, and the RAW Data List.  The desired output would include just column "OUTPUT". After I get this output data I will be joining into another data section, but for the purpose of this question to you all I only need help this part. 

Formula: essentially this list of News Paper Titles includes the team name, without the city within the title. However, I want to see the NFL name including the city name. So column F searches through column A and finds the NFL name, then it uses the corresponding column B (Ref Key) name to produce the OUTPUT. 

[AndrewDMerrill]

Is it guaranteed that each row in RAW Data List will have exactly 1 Search term (no more, no less)? If not, how do you want to handle those other cases?

[sshutchinson]

Hey Andrew, 

Yes it is possible that it won't include at term that is in the Text to Search...if it doesn't then it should return N/A or 0. The RAW data won't ever have two team names in one title. It could have both the NFL team name and the corresponding city name, in which case it will pick up the NFL team name and output the NFL team name and city name in the correct order. 

Do you follow?

Thank you for your help!

Stew

[AndrewDMerrill · Accepted solution]

Assuming a maximum of 1 search term per raw data item, the Find and Replace Tool is all that you need:

[sshutchinson]

Andrew, this was helpful and got me to the column I needed. Would you be able to help me get to the next step here?

The screenshot below consists on the pivot table that I currently have in Excel. Basically I want to replicate this exact outcome in Alteryx. 

I have the Find and Replace formula implemented. Now I just need to bring that back into the rest of the data and produce an output that looks like the Pivot. Do you have an recommendations for this step?