=IF(ISNUMBER(SEARCH to Exclude Similar Values

This formula seems to work for the most part for me put in B2 and drag down and across:

Formula:

Please Login or Register  to view this content.

A couple of inconsistencies. Column H title on Sheet2 did not have the repeating title "Reporting Org". I have added this to H1 so it now reads "Reporting Org Wealth". As the formula uses the naming convention to find the correct vertical2.
Next, All the columns match your desired results except for Wealth. This is because the sub org in Sheet2 column A "Private Bank & Wealth" does not match your translation table in Sheet1 "Private Bank & Wealth Group"
You would need to change your translation table on sheet1 and drop the "space" and "Group" from that definition so that it read "Private Bank & Wealth" in cell A19 of Sheet1.

Hi dosydos,

Thanks for the response, it is greatly appreciated. I think I have maybe not explained it clearly enough, so that's on me. Apologies. Sheet 1 was literally just there as reference and is not to be used for lookups etc.

On the actual worksheet I have to contend with, I need a formula to look into the text string within the sub org column (column A) and trigger the resulting org columns (B:I) so that they populate with the correct vertical 2(Reporting Org) ie 'Boston IC Branch' would trigger the ICB column.

At the same time I need to avoid things being triggered when not applicable like IM being shown when the text string includes the word 'Markets' because ‘IM’ should only trigger if 'Treasury Markets' or 'Capital Markets' are seen within the text string. ‘Markets’ belong under ‘ICB’ only.

Basically, if the text string found in the cell within Column A contains a sub org for one of the 7 Reporting Orgs it should populate the relevant column in the same row, and not populating cells incorrectly due to partial word matches like ‘Private Bank & Wealth,Markets’ triggering the CB&D column.

The CB&D cells/column should only populate where either ‘Private Bank’ or ‘Ukraine Funds’ are specifically seen and not where ‘Private Bank & Wealth Group’ is seen because it is falsely identifying it as CB&D based on the first 2 words of ‘Private Bank & Wealth Group’ matching ‘Private Bank’. Private Bank belongs within CB&D and Private Bank & Wealth Group belongs under Wealth
Not sure if I have made this a harder request or easier by removing the LOOKUP from the equation?

Any help is appreciated though.

Try this:

Please Login or Register  to view this content.

I've added a comma in front of "Banking" and "Markets". Having a lookup list would be by far the better option here instead of tricky nested IF statements.

Yes - just throw it back at them.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

They are already manually keying or pasting over certain cells/columns to remove false positives, so I'm sure they'll continue doing the same thing. If they are happy with the risk of manual errors and dont want new lookups that's on them.

I wasnt holding out much hope re the nested IFs working as I could find nothing online about excluding similar values, that I hadnt already tried, but thanks for the help anyway. Always like to double check with those with skill level is higher than mine.

Have a good bank holiday weekend.