Caterpillarr
New Member
- Joined
- Feb 25, 2018
- Messages
- 1
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!
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!