2 cell text lookup in text string to return data from separate column

Maybe, put this on C2 on sheet "Catalog" and copied down:

=IFERROR(IFERROR(LOOKUP(1000,SEARCH(SUBSTITUTE(Tags!$A$2:$A$8," ","*"),B2),Tags!$A$2:$A$8),LOOKUP(1000,SEARCH(LEFT(Tags!$A$2:$A$8,FIND(" ",Tags!$A$2:$A$8&" ")-1),B2),Tags!$A$2:$A$8)),"")

Thanks azumi,
the formula you provided doesn't do it (or needs some tweaking)
it returns values but not from column C of Tags sheet (not sure form where it retrieves the info)
attached what i got when i used it:


Screen Shot 2019-02-07 at 21.01.31.jpg

I played around with your formula, trying to get returned values from column C Tags sheet,

=IFERROR(IFERROR(LOOKUP(1000,SEARCH(SUBSTITUTE(Tags!$A$2:$A$8," ","*"),B4),Tags!$C$2:$C$8),LOOKUP(1000,SEARCH(LEFT(Tags!$A$2:$A$8,FIND(" ",Tags!$A$2:$A$8&" ")-1),B4),Tags!$A$2:$A$8)),"")

I was able to get returned values from column C Tags sheet but it seems it only checks against data from the "Type" columns, ignoring the 2nd criteria of validating that the text string in Title column has the text in Title column Tags sheet.

To clarify,
there's only 1 line in the sample file that matches both criteria:
Has a match in the Type column AND the text string in Title column includes the text in Title column Tags sheet.
all other lines should remain empty or display error.

something like this (which i created manually)

Screen Shot 2019-02-07 at 21.18.52.jpg

Bo_Ry my man !
your formula works perfectly :-)
it is the last part of a larger formula. after putting the part you provided it highlighted a different problem in the "main" formula that i didn't notice before.

this is the full formula:

IF(ISNA(VLOOKUP(E2,Tags!$G$1:$J$33,4,FALSE)),IF(ISNA(VLOOKUP(D2,Tags!$D$1:$E$13,2,FALSE)),VLOOKUP(E2,Tags!$A$2:$B$106,2,FALSE),VLOOKUP(D2,Tags!$D$1:$E$13,2,FALSE)),IF(ISTEXT(VLOOKUP(D2,Tags!$D$1:$E$13,2,FALSE)),VLOOKUP(D2,Tags!$D$1:$E$13,2,FALSE),IF(ISNA(LOOKUP(2^15,SEARCH(Tags!$H$2:$H$33,B2))),VLOOKUP(E2,Tags!$A$2:$B$106,2,FALSE),IFERROR(LOOKUP(1,1/(E2=Tags!$G$2:$G$33)/ISNUMBER(SEARCH(Tags!$H$2:$H$33,B2)),Tags!$J$2:$J$33),""))))&D2

The part highlighted in red is the part was not working as I need it to.
Its supposed to check for the existence of a word (from Tags!$H$2:$H$3) in the text string of B2,and it does that.
But, I need it to check for a "double" match (to confirm that the there's a match for column G and than that there's a match for column H of Tags sheet.

to solve it is used Bo_Ry (thanks again man) to replace my old Lookup formula.
it even made the entire formula shorter.

here is the full, new, working formula.

=IF(ISNA(VLOOKUP(E2,Tags!$G$1:$J$33,4,FALSE)),IF(ISNA(VLOOKUP(D2,Tags!$D$1:$E$13,2,FALSE)),VLOOKUP(E2,Tags!$A$2:$B$106,2,FALSE),VLOOKUP(D2,Tags!$D$1:$E$13,2,FALSE)),IF(ISTEXT(VLOOKUP(D2,Tags!$D$1:$E$13,2,FALSE)),VLOOKUP(D2,Tags!$D$1:$E$13,2,FALSE),IFERROR(LOOKUP(1,1/(E2=Tags!$G$2:$G$33)/ISNUMBER(SEARCH(Tags!$H$2:$H$33,B2)),Tags!$J$2:$J$33),VLOOKUP(E2,Tags!$A$2:$B$106,2,FALSE))))&D2