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.

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • 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 '18 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 '18 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 '18 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 '18 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 '18 at 0:20
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.

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.