Get a vlookup of a cell after split in Google sheet

user_5929430 0
Viewed 140

I have an incident Google form where there might be an input with ID numbers separated by . or / that have the same incident. I need in Google sheet to lookup the value of the ID numbers associated with the same incident and allocate it the same incident information.

I was able to split and jointext and transpose but I cannot get the incident information for each, and I need it to expand when new entries come in so an arrayformula is a must I think.

Here is what it looks like.

enter image description here

Here is what I was able to do with the formula

=TRANSPOSE(SPLIT(TEXTJOIN(".", 1, C2:C), "."))

enter image description here

And here is what I need as output. enter image description here

vlookup google-sheets-formula google-sheets
1 Answers

Try

=query(arrayformula(split(arrayformula(FLATTEN(split(A2:A4,".")&"|"&B2:B4)),"|")),"select * where Col2 is not null")

enter image description here

user_150193800
Related