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.