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]

*not suitable, working on alternative

 

[mrshl9898]

Are you looking for "PLM123-ABC"?

=IFERROR(INDEX(I:I,MATCH(TEXTJOIN("",TRUE,IF(ISERROR(MID(LEFT(A1,FIND("-",A1)-1),SEQUENCE(100),1)+0),MID(LEFT(A1,FIND("-",A1)-1),SEQUENCE(100),1),"")),H:H,0),1),5)

 

[santa12345]

Hi and thank you for the reply.

For PLM123-ABC . I would be looking for the category it belongs to. Using the chart above, which would be 1.

Another examples would be: I am looking for the category...

 

[mrshl9898]

Sorry, I was just asking if the brackets were included. Did you try the formula?

*Just change A1 to the location you are looking in, looks like M3

 

[santa12345]

Hi. .....the part # is actually in column H.
The table listing the parts and categories..is on the lookups tab. column F .(vs column H shown above)

so i changed your A's to H's and the H:H to lookups!F:F...

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

My result is 5 no matter what the part # starts with. Sorry - maybe i'm missing something.

 

[mrshl9898]

The I:I

Though none of those references seem to match your screenshots

 

[mrshl9898]

=IFERROR(INDEX(I:I,MATCH(TEXTJOIN("",TRUE,IF(ISERROR(MID(LEFT(A1,FIND("-",A1)-1),SEQUENCE(100),1)+0),MID(LEFT(A1,FIND("-",A1)-1),SEQUENCE(100),1),"")),H:H,0),1),5)

H:H is the "part" lookup column
I:I is the Category
A1 is the part you are looking for

 

[santa12345]

Here is what I have....

=IFERROR(INDEX(lookups!G:lookups!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:lookups!F,0),1),5)

lookups tab - lists the table..... column F has the part # - column G has the category
on the main tab... the parts i am trying to find the category are..in column H...

Sorry for the confusion.

is the different tab throwing the formula off?

 

[santa12345]

if i have the lookup table on the same tab...your formula works!!
Can i have the lookup table on a different tab?