Search for Multiple Strings then Return Result

Hello. I am not sure whether a macro or formula will be the most appropriate solution to my problem.

Please see attached. In the Initial sheet, the cells to fill are highlighted in yellow.

Basically, I would like to search for each attribute (Model, User, Color) in the Description column (col. I) and if all are found in the description then return the Part Number.

When the attribute is searched for, it does not have to be an exact match. For example, for cell E2, search for "Stark", "i337", "Tony", and "Red". Even though the description says "i337m", then i337 is found. Even though it says "ArmorTony", then "Tony" is found. And vice versa. Since all the attributes are in the description, it should then return the part number STKI337TONRED.

The desired result is in the Result sheet.

Any assistance would be much appreciated.

Hi.

Not sure I understand your expected results.

For row 3, "Bruce" is found nowhere within the string "Waynetech S560M Batmobile Black".

And for row 4, "Black" is found nowhere within the string "Waynetech I774 Utility Belt Bruce Yellow".

Regards

(Duplicate post: MODs/ADMIN please remove!)

Thanks.

Try this array formula**:

=INDEX(H$2:H$8,MATCH(COLUMNS(A2:D2),MMULT(0+(ISNUMBER(SEARCH(A2:D2,I$2:I$8))),TRANSPOSE(COLUMN(A2:D2))^0),0))

Copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).