0

Is there a simple formula for this?

Where it will look for a Name with "- Leader" in Column A and its corresponding Value in Column B, and it will return the Name of the Leader with the same Value as in Column B. (If that makes sense. hehe)

For example, if Apple is the Leader in Column A and its corresponding group in Column B is 1, Apple's name will return in Column C if Column B is 1.

I don't really know how to put this into words, but anyway, I have here attached an image of what I have wanted to achieve as a result.

I also wish to make it as an Arrayformula, where the formula is on the header(C1) only.

Any help will be very much appreciated! Thanks so much in advance!

enter image description here

1
  • Is the leader's name always contained in the other names of the same group? who assigns the numbering of the groups? is the list always already sorted by group number?
    – Daniele
    Mar 23 at 10:41

3 Answers 3

2

Given your sample data (wherein the "Leader" is always first in each grouping), clear Column C entirely (including the header) and place the following in C1:

=ArrayFormula({"Leader";IF(A2:A="",,VLOOKUP(B2:B,{B2:B,REGEXREPLACE(A2:A,"[\s-]*Leader","")},2,FALSE))})

This will produce the header (which you can change as you like within the formula itself), after which the formula will return null if A2:A is blank or will lookup the first number from B2:B within A2:A, returning everything up to but not including - Leader.

If your "Leader" is not always first in each grouping, use the following modified formula in C1:

=ArrayFormula({"Leader";IF(A2:A="",,VLOOKUP(B2:B,FILTER({B2:B,REGEXREPLACE(A2:A,"[\s-]*Leader","")},REGEXMATCH(A2:A,"Leader")),2,FALSE))})

6
  • Thank you soooo much, Sir. Erik! Your formula works amazing! I hope I could do more to show how grateful I am to all the good people who helped. Bless you, always, Sir! Thank you very much!
    – JDD
    Mar 24 at 8:28
  • I'm glad it worked for you. And thank you for marking the post as the correct answer.
    – Erik Tyler
    Mar 24 at 8:46
  • Good day Sir. @Erik Tyler! Is it possible to get the same result even without the help of column B? Thank you so much in advance, Sir!
    – JDD
    Aug 20 at 12:57
  • 1
    Yes, try this: =ArrayFormula({"Leader";IF(A2:A="",,VLOOKUP(ROW(A2:A),FILTER({ROW(A2:A),REGEXREPLACE(A2:A,"(?i)[^A-Za-z]+Leader","")},REGEXMATCH(A2:A,"(?i)Leader")),2,TRUE))})
    – Erik Tyler
    Aug 20 at 23:53
  • 1
    I'm glad it works as expected, and it's nice to see that this small thing has brought you joy and excitment.
    – Erik Tyler
    Aug 22 at 17:14
1

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.list of leaders

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.

1
  • Thank you so much, Sir. David! Your formula worked and thank you for the explanation, I have learned a lot. I am truly grateful. Sorry for my late reply. Always have a blessed day, Sir!
    – JDD
    Mar 24 at 7:51
1

This formula works but:

  1. it is not possible to enclose it in arrayformula
  2. assumes the list is sorted as in your image

=If(countif(A2,"*leader*"),Proper(Left(A2,SEARCH(" ",A2))), indirect("C"&row()-1)) image

3
  • Thank you so much, Sir/Ma'am Daniele!! Your formula also worked! And I think it's amazing 'coz I have learned many things. For that, I am really grateful. Have a blessed day always, Sir/Ma'am! (sorry I don't know which title to use because I have friends with the same name, the other one is a man and the other one is a woman.)
    – JDD
    Mar 24 at 8:37
  • thak you... I'm a man :)
    – Daniele
    Mar 24 at 11:45
  • oh got that, Sir. Daniele ^^ once again, thank you!
    – JDD
    Mar 24 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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