Excel pulling multiple wild card data from string to define type in other column

Question

Wondering if there is a better way to pull multiple partial texts from a string in one column to define a type for each line item in a separate column.

string examples:

• HEAVY HEX BOLT ASME B18.2.1 ASTM A320/A320M GR.B8M CL 2 PL UNC CLASS 2A SIZE: 5/8"" UNC X 2 1/2"" -
• VALVE TURNING DEVICE * MFR STD BRACKET AS PER DATA SHEET NO. : USSP-CBE-00-LT-000-T01-0713 -

my formula:

=IF(COUNTIF(W1292,"*support*"),"SUPPORT",
IF(COUNTIF(W1292,"*pipe*"),"SPOOL/PIPE",
IF(COUNTIF(W1292,"*bolt*"),"BOLT/NUT/WASHER",
IF(COUNTIF(W1292,"*washer*"),"BOLT/NUT/WASHER",
IF(COUNTIF(W1292,"*nut*"),"BOLT/NUT/WASHER",
IF(COUNTIF(W1292,"*valve*"),"FITTING",
IF(COUNTIF(W1292,""),"BLANK",
IF(COUNTIF(W1292,"*flange*"),"FITTING",
IF(COUNTIF(W1292,"*coupling*"),"ACCESSORIES",no)))))))))

I get an error every few hundred rows that says #NAME? Even though it works 95% throughout my spreadsheet.

I get an error every few hundred rows that says #NAME? Even though it works 95% throughout my spreadsheet. The partial text is valid in the column I'm pulling the data from so it should label the type but it doesn't.

Answer 1

You may benefit from SUMPRODUCT:

=IF(A1="";"BLANK";IFERROR(INDEX($E$2:$E$9;SUMPRODUCT(COUNTI(A1;"*"&$D$2:$D$9&"*")*ROW($D$2:$D$9))-1);"NO"))