VLOOKUP to Search Cell with Multiple Entries and Return a Single Value

Thank you for the reply. I couldn't get it to work. I'll try to attached sample spreadsheets soon.

Data2.xlsxDATA 1.xlsx

Search Column A in spreadsheet DATA1 and return value from column B in column B on Data2 spreadsheet.

My formula does work, for much of your data. Your files are different than your initial description. Here is the formula for the files you attached.

Formula:

Please Login or Register  to view this content.

However, it fails when you have more than one block of data per cell. I did a quick check and didn't see nonprinting characters in those cells except code 10 and 13, which seems to be present in the successful matches too. How do you load this data? It may require a more detailed analysis of your data than what I had time for.

Originally Posted by Microsoft Excel Help

When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. For more information on functions that you can use to clean text data, see Text and Data functions.

Thank you for taking a look at the spreadsheets - you hit the nail on the head with "when you have more than one block of data per cell". That's the challenge I had also...
The data is generated from a report in a system designed by ExpressTech.

Any additional input is appreciated.

I have done some more experimenting with this. VLOOKUP fails when the lookup cells exceed 255 characters. That appears to be well known within the Excel community but is not documented by Microsoft.

I tried using a combination of INDEX and MATCH but had exactly the same problem with MATCH. I found one similar solution but it doesn't work with wildcards.

There are also VBA solutions posted if you want to go that direction.