How to find the first cell in a row where the value is not empty and check if less or less than the number in another cell

Question

I have the following Google spreadsheet:

item have ready need1 need2 need3 A 1 2 1 B 1 2 1 1 C 2 2 

etc.

I want to populate the ready column as follows:

• find the first column in need1, ..., needN, which has a non-empty value
• if the found value is less than or equal to the value in the have column, set the ready column to something fun (e.g. yes )
• if the value found is greater than the value in the have column, do nothing

Thus, above the input, when the processed should look like this:

 item have ready need1 need2 need3 A 1 2 1 B 1 2 1 1 C 2 yes 2 

At the first stage, I found a suggested solution that did not work for me:

 =INDEX( SORT( FILTER( D10:H10 , LEN( D10:H10 ) ) , FILTER( COLUMN( D10:H10 ) , LEN( D10:H10 ) ) , 0 ) , 1 ) 

(he returns #REF! ) Not sure what is wrong with him, or how to proceed to the next step.

Thanks in advance!

Answer 1

If you know how many columns you need, or even how many columns are on the sheet, this is pretty simple. If not, and you need to look at the entire line, you may need to reconfigure the bit to avoid circular reference from the cell when the formula is part of that line.

The two second steps are pretty simple anyway - you need one of two results based on the condition, so you want to use =IF . Your condition is that the number “need” is less than or equal to the number “is,” and you want him to say “yes” if this is true, and nothing if it is not. So this gives us:

 =IF(need<=have,"Yes","") 

The examples below assume that your table above starts with cell A1 in the upper left and that the last column in your sheet Z

Next we need to find "need" and "have." Finding "have" is pretty simple - it's just the number in column B.

Finding a “need” is a little more difficult. You have the right idea using INDEX and FILTER , but your formula looks a little more complicated. Basically we can use FILTER to filter out empty values, and INDEX to find the first one that is left. First, FILTER :

The range from which you want to filter is all on the same line from column D to column Z (or regardless of the final column), and the condition you want to filter is that the same cells are not empty. For the formula that you enter in cell C2, we get:

 =FILTER(D2:Z2,D2:Z2<>"") 

Next, INDEX : if you give an INDEX array, row number and column number, it will tell you what is in the cell where this row and column meet. Since we filter out spaces, we just want everything else to be in the first column of our filtered array, which gives us:

 =INDEX(FILTER(D2:Z2,D2:Z2<>""),1,1) 

Or, since we have only one row in our array, and INDEX pretty smart, just:

 =INDEX(FILTER(D2:Z2,D2:Z2<>""),1) 

So, to put it all together, our final formula for cell C2 is:

 =IF(=INDEX(FILTER(D2:Z2,D2:Z2<>""),1)<=B2,"Yes","") 

Then just drag the formula down as many lines as you need. If your sheet or is getting wider, just change Z to any of your last column.

Answer 2

If you do not know the size of the range, use the row , column , rows , columns functions.

---

Simple formula

Here is an example of what you are looking for:

 =if(INDEX(FILTER(OFFSET(D2,,,1,COLUMNS(1:1)-column(D2)+1),OFFSET(D2,,,1,COLUMNS(1:1)-column(D2)+1)<>""),1)<=B2,"yes","") 

this part of the formula:

• OFFSET(D2,,,1,COLUMNS(1:1)-column(D2)+1)

returns the range starting from the given cell ( D2 ) to the end of the sheet ( COLUMNS(1:1)-column(D2)+1 )

---

Arrayformula

I suggest using ArrayFormula, it will expand automatically:

 =ARRAYFORMULA(if(REGEXEXTRACT(SUBSTITUTE(trim(transpose(query(transpose(OFFSET(D2,,,COUNTA(A2:A),COLUMNS(1:1)-column(D2)+1)),,COLUMNS(OFFSET(D2,,,COUNTA(A2:A),COLUMNS(1:1)-column(D2)+1)))))," ",", "),"\d+")*1<=OFFSET(B2,,,COUNTA(A2:A)),"yes","")) 

The column "Item" is assumed to have no empty values.

Answer 3

The solution from @Max Makhrov works and has the advantage of using a single formula for the entire column. However, it is assumed that all of your columns to the right of the ready (D) column will be need_ columns.

The solution from @dmusgrave also works if you remove the extra "=" before INDEX : =IF(INDEX(FILTER(D2:Z2,D2:Z2<>""),1)<=B2,"Yes","") . However, he makes the same assumption as well as the limits in column Z.

Such assumptions seem reasonable, but if they limit you, here is how you can have any number of need_ columns, starting with your ready column:

=IF(INDEX(FILTER(INDIRECT( "D"&ROW()&":"&CHAR(67+COLUMNS(FILTER($1:$1,LEFT($1:$1, 4)="need")))&row() ), INDIRECT( "D"&ROW()&":"&CHAR(67+COLUMNS(FILTER($1:$1,LEFT($1:$1,4)="need")))&row() )<>""),1)<=B2,"Yes","")

The idea is to simply replace D2:Z2 (in @dmusgrave's solution) with:

INDIRECT( "D"&ROW()&":"&CHAR(67+COLUMNS(FILTER($1:$1,LEFT($1:$1, 4)="need")))&row() )

Explanation: You start with D in the current row, and you go to the last column need_ in the same current row. CHAR(68) - D , to which you add the number of columns called need.* , Minus one (hence 67).

Using the same logic, you can easily make your formula more reliable / universal, for example, without need_ columns starting on the right side in the ready column, etc.