Google Sheets, using VLOOKUP and ARRAYFORMULA to search multi-line cells

Question

I'm trying to use VLOOKUP to find the batch number of a product based on its serial number. The problem is that the serial numbers for each batch are stored in one cell per batch, separated by newlines. For example, batch one (B01) may have serial numbers (001, 005, 127, 010) and batch two (B02) may only have one serial number (098). Each serial number is unique. I want to manually input the serial number and find the batch number associated with it.

The other big issue is that I would like to use ARRAYFORMULA to automatically 'drag' the formula down as I will be constantly adding new rows to the sheet. I've tried numerous combinations of ARRAYFORMULA, VLOOKUP, SPLIT, FLATTEN, CONCATENATE, FILTER, UNIQUE, QUERY, and more but with no success.

I'm relatively new to Google Sheets formulas and automation so any help would be greatly appreciated and any extra explanation would also be greatly appreciated (though not necessary). Below is a picture of my end goal.

Answer 1

You can use wildcards in vlookup() like this:

=arrayformula( 
  if( 
    len(E7:E), 
    vlookup(
      "*" & E7:E & "*", 
      { B2:B, A2:A }, 
      2, 
      false 
    ), 
    iferror(1/0) 
  ) 
)