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

Question

I've got 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 fill ready column as follows:

• find the first column in need1, ..., needN range which has a non-empty value
• if the value found is less or equals the value in have column, set ready column to something cheerful (e.g. yes)
• if the value found is larger than the value in have column, don't do anything

So above input, when processed should look like this:

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

For the first step I found a suggested solution, which did not work for me:

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

(it returns #REF!) Not sure what's wrong with it or how to proceed to the next step.

Thanks in advance!

Answer 1

If you know how many need columns you have, or even just how many columns are on the sheet, this is quite straightforward. If not and you need to look at the entire row, you might have to redesign a bit to avoid a circular reference from the cell with the formula being part of that row.

Your second two steps are fairly simple either way - you want one of two results based on a condition, so you're going to want to use =IF. Your condition is that the 'need' number is less than or equal to the 'have' number, and you want it to say 'yes' if that's true, and nothing if it isn't. So, that gives us:

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

The examples below assume your table above starts from cell A1 in the top left, and that the last column in your sheet is Z

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

Finding 'need' is slightly more complicated. You've got the right idea using INDEX and FILTER, but your formula seems a little overcomplicated. Basically we can use FILTER to filter out the blank values, and INDEX to find the first one that is left. First, FILTER:

The range you want to filter from is everything in the same row from column D to column Z (or whatever the final column is), and the condition you want to filter for is that those same cells are not blank. For the formula you're typing into cell C2, that gives us:

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

Next, INDEX: If you give INDEX an array, a row number, and a column number, it will tell you what is at that the cell where that row and column meet. As we've filtered out the blanks, we just want whatever is left in the first column of our filtered array, which gives us:

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

Or, as we only have one row in our array, and INDEX is pretty smart, simply:

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

So to bring 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 for as many rows as you need. If your sheet is or becomes wider, just change Z to whatever your last column is.

Answer 2

When you don't know the size of a range, use functions row, column, rows, columns.

---

Simple formula

Here's an example of what you are looking:

=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 formula:

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

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

---

ArrayFormula

I suggest using ArrayFormula, it'll 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",""))

It assumes that 'Item' column has no blank values.

Answer 3

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

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

Such assumptions seem reasonable, but if they are limiting you, here's how you can have any number of need_ columns starting right of 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 simply to replace D2:Z2 (in @dmusgrave's solution) by :

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

Explanation: You start from D at current row, and you go until the last need_ column on the same current row. CHAR(68) is D, to which you add the number of columns titled need.*, minus one (hence the 67).

Using the same logic, you can easily make your formula more robust/generic, such as not having the need_ columns starting right form the ready column, etc.