0

enter image description here

Goal here is to have the sheets search a row, then once it finds the first instance of >=0, return the cell value that contains the month+concatenate the value in the cell next to it (or any other cell of choice). I've confirmed I can do this with a series of nested IF statements, but am looking for something more efficient. I'm not too terribly familiar with writing my own scripts, but can if someone can give general direction.

Improve this question
6
  • Welcome. Usually if something could be done by using built-in formulas it would be more efficient than using a script. Regarding asking "a general direction" this is too broad. Do you already know the basics about writing Google Sheets custom functions?
    – Rubén ♦
    Nov 19, 2018 at 22:21
  • Hi Rubén, I was able to do this with about 24 nested IF statements, so technically I was able to do it with built-in formulas. For conversation sake, let's assume I'm completely illiterate when it comes to writing custom functions. I know where to write them, and the very basic idea of a For Loop, etc.
    – Ryan
    Nov 19, 2018 at 22:50
  • If we should assume that the question is from a completely illiterate point of view, then the question is too broad. Please edit the question to make it specific and to add a brief description of your search/research efforts. Ref. How to Ask.
    – Rubén ♦
    Nov 19, 2018 at 23:33
  • Thanks for the note Rubén, I updated to include a better detailed explanation, as well as a sample sheet of data. I also just saw your answer below and will give it a shot.
    – Ryan
    Nov 20, 2018 at 0:10
  • Unfortunately the recent revision invalidates the current answer, that from my point of view correctly answered the previous revision, so the previous revision be reverted and the current revision should be posted as a new question.
    – Rubén ♦
    Nov 20, 2018 at 0:20

1 Answer 1

Reset to default
0

From rev 1:

Please note that the month is above the value.

Formula

=ArrayFormula(INDEX(SPLIT(JOIN(",",IF(C2:2>=0,$C1:$1,)),","),1))

Explanation

  • $C1:$1 refers to the headers row C2:2 refers to the values row.
  • IF returns an array where values equal and greater than 0 are included and blank is included for values that are less than 0.
  • JOIN creates a string using a comma to separate array values.
  • SPLIT splits the string omitting the blank members.
  • INDEX returns the first member.
Improve this answer

Your Answer

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

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