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!

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

- 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

- 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.

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel