Hello, all. First time in a forum so if I make a few foopars please go easy on me.
I have a spreadsheet which contains a column of car makers (Col A). In another column (Col C) I have a comment about a particular model in that makers range. I want to create a formula which searches the text comment for a keyword (the model) and return that keyword (model name). The keywords (models) in reality are listed in another spreadsheet in columns under the makers name. However I have uploaded a spreadsheet as an example with it all in one. The models are listed under makers names column headings in named ranges to make it easier to add models in the future.
I managed to get it partly working using the SEARCH function but it struggled with the named ranges and I ended up with a huge nested formula which wasn't very elegant and needed a lot of manual editing for each cell. The solution will actually be run over a 2000 row spreadsheet so it needs to be draggable. I have also tried to use a nested Match but tied myself in knots. Any suggestions?
Many Thanks
Ian99
Discrete range Keyword searrch text string problem.xls
Bookmarks