Link to my sheet: https://docs.google.com/spreadsheets/d/19wEySRlrXNsEy49vavXi5lBNnqRv0W4PqLACI_MlM5I/edit?usp=sharing
First you need to set up a table of leaders, you can do this in another sheet then hide it later.
You can make this manually, or you can use this function. Btw, if you want to use this function, you still have to drag down the number, as it won't do it automatically for you. That' simple enough though.
=TRANSPOSE(
REGEXEXTRACT(
TEXTJOIN(" ",TRUE,Sheet1!A:A),
REGEXREPLACE(
TEXTJOIN(" ",TRUE,Sheet1!A:A),
"(\w+) - Leader",
"($1) - Leader")
)
)
=ARRAYFORMULA(VLOOKUP(B2:B7,A2:B3,2)) Place this formula in C2 (below C1). It should work now. I couldn't figure out a way to place it onto C1.
Explanation
I took inspiration for the regex extraction from here: https://stackoverflow.com/questions/43432409/multiple-regex-matches-in-google-sheets-formula. It basically takes the A column from Sheet1 and turns it into a giant long text. It then wraps all the matching patterns in parenthesis. Then it extracts all of it then transposes it to become a column.
VLOOKUP() takes in 3 argument. So the first argument is the value it will compare to the table with. Since we're using ARRAYFORMULA(), we can put in a range where we normally can't. Next argument is the table. We exclude the header when referencing it, so we use A2:B3. Lastly is the index, which just tells the function which column to spit out. We want the second column, so we put in 2. Just make sure your table of leaders are sorted correctly or else VLOOKUP() will fail.
Other notes: Do extend the A2:B3 range if you plan on adding more leaders. This also goes for B2:B7 or else the Array formula won't take them into account.
