How to return comma separated data (already set up) and return data into separately (matched) cells

[tess1975]

Hi,

I have an example sales order list in range (H2:M8), I use one row per item ordered but can sell up to 8 items on that one row. I've added a column to the list (Column M) for serial number(S) of items sold.

So now I have a list of serial numbers (A2:A32) and the column next to it (BB1:B32) is where my formula is because here i want to know which serial numbers have been used/allocated to sales orders.

I have set this up, have tried different formulas I've found which work but only when ONE serial number has been used. When more than one serial number is used, he serial numbers appear available to use. How can I split the multiple values returned (serial numbers "used") into their rightful place next to their equivalent serial number. How do I tell Excel that the multiple serial numbers in one cell are not one long reference and need to be split up and return multiple values?

Currently I've used both below formulas below and they return the same information but only when one serial number is found.

=IF(ISNA(VLOOKUP(A2,$M$2:$M$8,0,FALSE)),"-","used")
=IF(ISNUMBER(MATCH(A2,$M$2:$M$8,0)),"used","-")

 

[Fluff]

Hi & welcome to MrExcel.
Try

=if(countifs(M:M,"*"&A2&"*"),"Used","-")

 

[tess1975]

Hi & welcome to MrExcel.
Try

=if(countifs(M:M,"*"&A2&"*"),"Used","-")

LEGEND! Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.