Using a lookup table for an IF function?

[Caterpillarr]

Hi all. I have a spreadsheet that I use to track my finances and I have come across an issue (I am well aware that there are tools that will do it for me, but this is puzzling me and I need to solve it for my own sanity/sense of achievement!). Essentially, I'm currently having to use a HUGE nested IF statement, allowing for about 20 different scenarios which is obviously not ideal - especially when it comes to adding another scenario and having to manipulate the IF again etc etc.

So, I wondered if anyone could help me with potentially using some form of lookup table with an IF? What I would like to do is IF a cell contains a certain string, to return a corresponding string. For example, I have a column that shows raw vendor names such as "TESCO 123456", "J F SMITH", "COSTA COFFEE 12" and I have an adjacent column that normalises this data using a formula that says if the cell contains "tesco" to return "Tesco", "J F SMITH" to return "John Smith", "costa" to return "Costa" etc etc. I hope that makes sense. I currently have it set up using ISNUMBER(SEARCH()) within a huge nested IFS function, and I know this isn't the best solution.

Happy to clarify anything as I know my explanation isn't great, thank you in advance!

 

[SpillerBD]

You could use a MATCH with a CHOOSE instead, INDEX(MATCH()) or few others.
However, beyond the variations of nested IF statements, knowing the exact reasoning for your scenarios leaves a lot of questions.

 

[Michael M]

I'm guessing, if there many and varied changes to be made, you'd be better applying a VBA code to the columns.
That would also allow the data to be manipulated in the existing column rather than having a helper column !!