Search code examples
stringlistgoogle-sheetsformulaspreadsheet

Write data from a list if the adjacent cell contains certain string

I need to update hundreds of cells, and that would be trivial automating, but I am not being able to make it work.

I have a list like the following:

Source list

And, in a different tab, a list I have to populate with values above (in B) based on the appearance of the twitter handle in other column.

Data destination

The names are within a long text string (all of them begin with @), and it is not possible to re-order the list based on those names. Also, there are more names than values, so some cells will remain blank.

Is there a way I can write a formula that writes the values of the first list into the second one if the name in column A in that row is contained within the adjacent string?

Thanks!

Solution

  • You can refer to this sample formula (Same sheet was used):

    =arrayformula(if(C2:C<>"",iferror(vlookup(REGEXEXTRACT(C2:C,"\B\@\w+"),A2:B,2,false),""),""))

    What it does?

    • Use array formula to loop column C values
    • Extract the twitter name (string that starts with @) using Regexextract()
    • Use the extracted @twittername as search key to get the connections value using vlookup()

    Output:

    enter image description here