Need help to find the serial number if data match

Hi,
What formula should I use to search if cell includes lot of number separate by a comma. I used Index-Match but it did not work

Exp: I want to find the S/N for 123 in sheet1 and below data is in sheet2

***Result should give me S/N is 1

S/N Part
1 123, 234, 345, 456-4, 5678-0
2
3

Regards,
tt3

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Dear TT3, Plz see yellow banner on top of the screen regarding "HOW TO ATTACH YOUR SAMPLE WORKBOOK:"
Attach your sample work book with desire result.

Please try at C2

=MATCH(0,INDEX(-FIND(" "&B2&","," "&Data!$B$2:$B$4&","),))

Thanks for the rep.

Please try

Please Login or Register  to view this content.

Please try

Please Login or Register  to view this content.

FIND(" "&B2&","," "&Data!$B$2&",")
FIND(" "&123&"," , " "&"123, 234, 345, 456-4, 5678-0"&",")

Add " " before and "," behind to get full word between delimeter search.
Without add " " and "," 23 can be found in "123, 234" which is not correct

But " 23," can not be found in " 123, 234,"
and " 123," can be found in " 123, 234,"

Word found return number, and !VALUE# if not found
=MATCH(0,INDEX(-FIND(" "&B2&","," "&Data!$B$2:$B$4&","),))
Index is use for do array calcuation without the need of Ctrl+Shift+Enter

Aproximate match lookup for lookup value less than 0 and return position of number.
Match(0, -{#VALUE!;#VALUE!;10})

or than lookup value less than 9^9 and return position of number.
Match(9^9, {#VALUE!;#VALUE!;10})

Serial number is order by 1,2,3 . Position is the same as Serial number

If Serial number is not 1,2,3 eg 1a,2a,3a

Index of Data!$A$2:$A$4 is need to use with Match

=INDEX(Data!$A$2:$A$4,MATCH(0,INDEX(-FIND(" "&B2&","," "&Data!$B$2:$B$4&","),)))

We also can use Lookup to get the same result
=LOOKUP(0,-FIND(" "&B2&","," "&Data!$B$2:$B$4&","),Data!$A$2:$A$4)