My results are to be stored in column C in Sheet 1
Sheet 2 has the data I'm searching for. I want to extract the data from
column d (always a number) on sheet 2. The Type must be "Y", Names will be an
exact match, and the category (column b) will always match at the beginning,
category on Sheet 1 has varying lengths.

Sheet 1
column a column b column c
AA Name 1 1
BBB Name 2 6,14,30

Sheet 2
column a column b column c column d
Y AA.345 Name 1 1
Y AA123 Name 2 2
Y BBB1 Name 2 30
Y BBB2 Name 2 14
N bBB23 Name 2 5
Y BBB3435 Name 2 6
Y bb12 Name 2 7