HI,
I am trying to replicate a =IF(ISNUMBER(SEARCH(Key!$T$5,$B4)) in Alteryx. Currently I have an excel file with a list of file paths. I need to extract key data then replace in appended column a response based on the key data.
I am using the Find Replace tool but it seems to be returning responses based on the order it appears in the string and not the order of the lookup table.
Example string to search:
T:\Income\2020\02) Provision\Outlook Q1\A) WNR & Subs\2020.Q1 Automated Entries.xlsx
Example look up table
| SEARCH | RESULT |
| Auto | Entries |
| Internal Controls | Internal Controls |
| Correspondence | Correspondence |
| Branch | Branch Info |
| R&D | R&D Credit |
| Calendar | Calendar & Schedules |
| Return | Return |
| REIT Checklist | REIT Testing |
| Provision | Provision |
The Excel answer would be "Entries" since it looks up the first entry on the table "Auto", sees if its in the string then returns the answer "Entries", if the first entry doesn't exist in the string, it moves to the second entry and so on. The order of the lookup table matters.
The Alteryx answer is "Provision". It seems to be looking at the order it appears in the string.
How do I get the Find and Replace to use the order of the lookup table, not the order of the string? Or is there a different way to do this? I have rather large tables to look up against and don't want to build these into a formula.
Thanks
