IF vs Vlookup for text that contains

[Kelli Webb]

Hello,

What I would like to do is use the most adaptable formula to return a classification Column E based on the Position title Column D; There are only 8 classifications.

Ideally, I would like to use IF so regardless of what a new title is, if it contains one of the classifications it will be picked up. I've tried to use *wild cards, but it doesn't work well if IF statements.

My issue is how am I able to differentiate between Positions with Solicitor = Solicitor, & Senior Solicitor should = Senior Solicitor etc.

I know Vlookup would be the easiest, but I don't want to add it to the list of workbooks that need to be constantly updated every time a new role is created.

I really would appreciate any help with this.

I hope you & your family are staying safe!! Kelli

 

[p45cal]

In the attached, column J contains a formulae. They're array-entered, meaning the formulae have been committed to the sheet using Ctrl+Shift+Enter, not just Enter.
It is important that the Class list in column H is sorted, longest strings at the top.

Separately, there's another formula in column K, also array-entered, which will give all strings found but you will need Excel version 2016 at least, for the TEXTJOIN function to be recognised (I think you're using Excel 2013).

As for those red cells 'should be blank', that gets more complex; we'd need rules to follow to determine why they should be blank.