Vlookup; lookup cell contains multiple strings to search for

[jamie1985]

Hi

I am building a sheet which gives scores to customer based on their answers in an application form. This uses simple lookups to match answers to a scoring sheet and then return the relevant score. One of the questions allows multiple answers (motivations for purchase) to be selected and this is causing me difficulty.

In the dataset, if the customer has selected multiple options they appear in one cell separated by commas. I need to be able to apply the scores to the customers with multiple selections, and if possible would like to try two ways of doing this.

1. Find all of the answers (strings) that appear for each customer and add the relevant scores together, returning the total
2. Return the highest individual score

I'm not sure if this is even possible... Any help would be appreciated.

Thanks

J

[Glenn Kennedy]

Pretty much everything is possible.. but the possibility increases significantly if we have a spread sheet to work with!! Can you post your sheet ... or a sample of it... (MINUS any confidential information)?

[jamie1985]

Sorry, here's a small example.

Example Workbook.xlsx

You will see that on the data page scores are allocated for 'language' and 'when begin', using lookups to the other pages. The problem is with the motivation score, as customers can select multiple answers and points are awarded for individual answers (scores page).

I hope this helps. Thanks

[Glenn Kennedy]

Mmmm. Yes it can almost certainly can be done, but... The format of the text in column F does not match the descriptors in Scores column AI (e.g. qualification-certificate in one place and qualification/certificate in the other). How is column F populated? Can you use a number of columns (rather than 1) and use data validation to ensure that the same spelling/punctuation is used?

[jamie1985]

Oh sorry, I had fixed this issue for the other factors but had not got to it for motivation as I was stuck immediately.

I attach an edited version - I have changed the text on the scores sheet as all new data will come in the format shown on the data page.

Example Workbook.xlsx

Further to your comment, I was thinking using multiple columns would be good/simple. Customers can choose a max of 6 answers so is it possible to do something that will extract the answers that are separated by commas into different cells? I could then simply use lookups on these and add them together/find max....

Thanks Glenn

[jamie1985]

Hi

I've been able to find a solution, using the text to columns function and some vba to run it. Not sure if this count as solved..?