Index Match Formula in Excel

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.

Alteryx Community Post.png

Alteryx Community Data.xlsx

Workflow

Datasets

Developer Tools

Output

Transformation

Preparation

Optimization

Expression

Database Connection

Accepted answers

CoG

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

Find and Replace 1.png

Find and Replace 2.png

Find and Replace 1.yxmd

All comments

CoG

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

CoG

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

Find and Replace 1.png

Find and Replace 2.png

Find and Replace 1.yxmd

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?

Alteryx Community Question 2.png

NFL Project Workflow.yxmd

NFL Project Raw Input Data.xlsx

NFL Project Search Terms.xlsx

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335