Search code examples
excelexcel-formularange

Excel formula that returns a range of non empty cells based on conditions

In the example below I'd like to enter in cells L4, M4 and N4 a formula that will return all the flavors (range C:C) for a given product (range B:B). For example, in cell L4, the formula should look at the data in L3, goes to B3 (because equals to L4), and returns all non empty cells in the range C4:C9 (row B3 +1 to row B10-1, until it finds another non empty cell in column B or end of the table basically).

Note:

  • There will always be three rows between the end of the recipe for a product for a given flavor and the next product.
  • There will always be two rows between the end of a recipe for a given flavor and the next recipe for another flavor for the same product

Thanks in advance for your help with this!

Screenshot

I've tried a combination of Index, Small and Rows, but can't find the right formula...

Solution

  • This formula combination may be a little complex, so let me use line break to explain

    =FILTER(
OFFSET($B$1,
        MATCH(L$3,$B:$B,0),
        1,
        MATCH(TRUE,NOT(ISBLANK(OFFSET($B$1,MATCH(L$3,$B:$B,0),0,9999,1))),0)-1,
        1), 
OFFSET($B$1,MATCH(L$3,$B:$B,0),1,MATCH(TRUE,NOT(ISBLANK(OFFSET($B$1,MATCH(L$3,$B:$B,0),0,9999,1))),0),1)<>"")

    OFFSET => The formula to get a range with relative postition from a reference field. We take B1 as the reference field

    OFFSET have 5 parameters, (reference field, offset row, offset col, range height, range width) Reference field = B1

    Take L4 as example

    • offset row = start from the value match L3 => MATCH(L$3,$B:$B,0)
    • offset col = the actually value we need is col C, 1 col offset to B => 1
    • range height =
      • OFFSET($B$1,MATCH(L$3,$B:$B,0),0,9999,1) => Get the row after B3
      • NOT(ISBLANK(OFFSET($B$1,MATCH(L$3,$B:$B,0),0,9999,1))) => Check those fields not blank after B3
        • MATCH(TRUE,NOT(ISBLANK(OFFSET($B$1,MATCH(L$3,$B:$B,0)-1,0,9999,1))),0) => Get the next field after B3 and not blank, Which is B10, will be 7
          B10 - B3 = 7
          we do not need to cover B10, so -1 = 6
    • range width = only one col is ok, we only need the col C => 1

    Then we can get the not blank content in C4 : C9

    Formula will be like this in one line:

    =FILTER(OFFSET($B$1,MATCH(L$3,$B:$B,0),1,MATCH(TRUE,NOT(ISBLANK(OFFSET($B$1,MATCH(L$3,$B:$B,0),0,9999,1))),0)-1,1),OFFSET($B$1,MATCH(L$3,$B:$B,0),1,MATCH(TRUE,NOT(ISBLANK(OFFSET($B$1,MATCH(L$3,$B:$B,0),0,9999,1))),0)-1,1)<>"")

    BUT ONE THING TO NOTICE, in the end of product C, need some random text to show there is no more data after the row. enter image description here