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.

enter image description here


1 Answer 1


You can use wildcards in vlookup() like this:

      "*" & E7:E & "*", 
      { B2:B, A2:A }, 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.