I've thrown together a script based solution for you. Other solutions that require a formula on every line where you might have partials will end up bogging down the sheet by quite a bit for large data sets. This should generate a range of matches after a couple seconds for data several tens of thousands of rows long.
Note: Since you opted to not provide a sample dataset, I had to assume how it's laid out. However, this will work regardless of where your columns are, as long as they are titled as Full Words, Partials, and Matches.
Link to spreadsheet (Must be signed into a google account to use the button): Google Sheet
Just click the Get Matches button to have it generate the matches.
The source is a bit more complex/dynamic than it needs to be, but I had a bunch of functions already laying around that I just reused.
Source:
//Retrieves all the necessary word matches
function GetWordMatches() {
var spreadsheet = SpreadsheetApp.openById('1s0S2iJ7L0wEXgVsKrpuK-aLysaxfHYRDQgp3ShPR8Ns').getSheetByName('Matches');
var dataRange = spreadsheet.getDataRange();
var valuesRange = dataRange.getValues();
var columns = GetColumns(valuesRange, dataRange.getNumColumns(), 0);
var fullWordsData = GetColumnAsArray(valuesRange, columns.columns['Full Words'].index, true, 1);
var partialsArray = GetColumnAsArray(valuesRange, columns.columns['Partials'].index, true, 1);
var partialsData = GeneratePartialsRegexArray(partialsArray);
var matches = GenerateMatches(fullWordsData, partialsData);
WriteMatchesToSheet(spreadsheet, columns.columns['Matches'].index, matches, partialsArray);
}
//Writes the matches to the sheet
function WriteMatchesToSheet(spreadsheet, matchesColumnIndex, matches, partialsArray){
var sortedMatches = SortByKeys(matches, partialsArray);
var dataRange = spreadsheet.getRange(2, matchesColumnIndex+1, sortedMatches.length);
dataRange.setValues(sortedMatches);
}
//Generates an array of matches for the full words and partials
function GenerateMatches(fullwordsData, partialsData){
var output = [];
var totalLoops = 0;
for(var i = 0; i < fullwordsData.length; i++){
totalLoops++;
for(var ii = 0; ii < partialsData.length; ii++){
totalLoops++;
var result = fullwordsData[i].match(partialsData[ii].regex)
if(result){
output.push([fullwordsData[i], partialsData[ii].value]);
partialsData.splice(ii, 1);
break;
}
}
}
if(partialsData.length > 0){
var missedData = GenerateMissedPartialsArray(partialsData);
output = output.concat(missedData);
}
return output;
}
//Generates a missed partials array based on the partials that found no match.
function GenerateMissedPartialsArray(partialsData){
var output = [];
for(var i = 0; i < partialsData.length; i++){
output.push(['No Match', partialsData[i].value])
}
return output;
}
//Generates the regex array for the partials
function GeneratePartialsRegexArray(partialsArray){
var output = [];
for(var i = 0; i < partialsArray.length; i++){
output.push({regex: new RegExp(partialsArray[i], 'i'), value: partialsArray[i]});
}
return output;
}
//http://stackoverflow.com/a/13305008/3547347
function SortByKeys(itemsArray, sortingArray){
var itemsMap = CreateItemsMap(itemsArray), result = [];
for (var i = 0; i < sortingArray.length; ++i) {
var key = sortingArray[i];
result.push([itemsMap[key].shift()]);
}
return result;
}
//http://stackoverflow.com/a/13305008/3547347
function CreateItemsMap(itemsArray) {
var itemsMap = {};
for (var i = 0, item; (item = itemsArray[i]); ++i) {
(itemsMap[item[1]] || (itemsMap[item[1]] = [])).push(item[0]);
}
return itemsMap;
}
//Gets a column of data as an array
function GetColumnAsArray(valuesRange, columnIndex, ignoreBlank, startRowIndex){
var output = [];
for(var i = startRowIndex; i < valuesRange.length; i++){
if(ignoreBlank){
if(valuesRange[i][columnIndex] !== ''){
output.push(valuesRange[i][columnIndex]);
}
continue;
}
output.push(valuesRange[i][columnIndex]);
}
return output;
}
//Gets a columns object for the sheet for easy indexing
function GetColumns(valuesRange, columnCount, rowIndex)
{
var columns = {
columns: {},
length: 0
}
Logger.log("Populating columns...");
for(var i = 0; i < columnCount; i++)
{
if(valuesRange[0][i] !== ''){
columns.columns[valuesRange[0][i]] = {index: i ,value: valuesRange[0][i]};
columns.length++;
}
}
return columns;
}
A note on some decisions: I opted to not use map, or other more concise array functions for the sake of performance.
