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.
-
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.– RyanNov 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.– RyanNov 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
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 rowC2: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.