I am trying to do a partial match of Products (Column B) to DB (Column H). Column B however has many partial names within the cell. The entirety of Column H needs to be searched for a partial match of Column B, if a match is found then the cell name from column H needs to be input into column C.
Link to sample Google sheet: https://docs.google.com/spreadsheets/d/1ZvIbZQ9zsLd6w1uGgSbQ3v-QFebMYrlQfvdsLCfEwAg/edit?usp=sharing
Any advice on how to do this would be appreciated.
In order to look for a substring that would correspond to all the cell values from H2:H70 in B2 (and the subsequent values of the column B) you will be wanting to use the function REGEXMATCH that compares a text (in the case below B2) with a regular expressing (each of the values from H2:H70). If it finds a match then it returns the value of column H by using the IF function and if it doesnt it returns empty.
As we are comparing the range H2:H70 to B2 this will return an array of possible matches or not matches (emtpy after the IF) and therefore this array must be handled with ARRAYFORMULA.
Then we are using QUERY to only return the array values that are not empty. This will leave us with an array of values of just the actual matches found (in case there are more than one). As I imagined that you want all the matches in a single cell I am taking all the matches array values and joining them in a single array separated by a space using TEXTJOIN.
Finally, I am using IFERROR to return empty in case no matches were found.
=IFERROR(TEXTJOIN(" ",TRUE,QUERY(ARRAYFORMULA(IF(REGEXMATCH(B2, $H$1:$H$70),$H$1:$H$70,"")),"where Col1 is not null")))
