Vlookup with Multiple Answers in Lookup Cell!

Hi, and welcome to the forum

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely much use and not many of us want to spend time recreating your workbook when you already have one.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

This entered in Data!E2 and filled down will return the value associated with the criteria in Data!C2 from the range in Criteria.

Formula:

Please Login or Register  to view this content.

You have accomplished the same thing using VLOOKUP. You haven't supplied what you really want returned into Data!column E. Can you supply an example of what exactly you are looking for as it isn't at all evident?

I don't know why you have multiple reasons entered into one cell. That becomes a nightmare to work with. There is nothing wrong with using VLOOKUP but the data must be set up to be handled correctly. Where there is more than one reaon for failure there should be either another row entered or columns to accommodate more than one reason. In short, one cell per reason.
Here is your workbook back showing 1 row per reason and makes use of column A on the Criteria worksheet instead of looking up "Question....." against a column of text. Using the number in column A eliminates the possibility of making a lot of errors. In addition, using full column references isn't a good idea especially if the data becomes long. I converted the Criteria data to a table and gave the table a name called Failures and used that in the formulae.

I think that your problem with using the numbers is that in your VLOOKUPs in the workbook, you are using wildcards. If you use the numbers, no wildcards should be used.

Column C, Reason for Failure, is quite inconsistent. Elements of the Question are missing and some commas are missing. You will see what I mean if you select column C and use Text To Columns with COMMAS as the delimiters. If that data was pristine, it would be a simple matter to separate the reasons into separate columns. Even that has problems because you don't know how many columns that will be required as there are instances where there are more than 2 or 3 reasons.

A reliable formula to straighten this out is in my opinion not likely.

A possible VBA solution could extract the Question numbers from the text in column C and those numbers could be used to lookup the descriptions from the Criteria worksheet. This is something that I can't do for you.