Excel - Parse Data and Categorize Based On Criteria

[santa12345]

Hello.
I am trying to do the following:

I have a part numbers listed in Column A. Based on how the part number starts, I want to categorize it appropriately.

Based on the following:

A1 = has part # (PLM123-ABC)
I want B1 to return 1 (for the category)

A2 = has part # (ABC-123-XYZ)
I want B2 to return 5 (for the category)

Is there a way to do this ?

Thank you for your time and advanced input.

 

[mrshl9898]

close, different tab is fine

=IFERROR(INDEX(Lookups!G:G,MATCH(TEXTJOIN("",TRUE,IF(ISERROR(MID(LEFT(H2,FIND("-",H2)-1),SEQUENCE(100),1)+0),MID(LEFT(H2,FIND("-",H2)-1),SEQUENCE(100),1),"")),Lookups!F:F,0),1),5)

 

[santa12345]

And lastly..and unfortunately some of these parts don't have a "-" after the first few characters.
So this part...

SB38 X 12 X 8.000IN comes back with a 5. instead of a 1. as a category. I think because there is no "-" Wondering if the formula could be changed to only include the first 2 characters.

 

[mrshl9898]

Sure

=IFERROR(INDEX(lookups!G:G,MATCH(TEXTJOIN("",TRUE,IF(ISERROR(MID(LEFT(H2,2),SEQUENCE(100),1)+0),MID(LEFT(H2,2),SEQUENCE(100),1),"")),lookups!F:F,0),1),5)

*just make sure you change the lookups to also be just 2 characters