Returning an array using offset function in excel?-CodePudding

I am unsure how to get this array... Maybe offset is the wrong function for this. But I want to return an array based on multiple criteria. Here are the two datasets it's based off of:

Basically, I would want the output to give me an array that, when entering a date, it would return what is in DataSet 2, but also on the condition that it is "In Stock" as shown in DataSet 1

=OFFSET(Sheet1!$C$3,MATCH(A10,Sheet1!$A$2:$A$50000,0),MATCH(A3,Sheet1!$B$1:$BX$1,0))

I know the following formula would just give me the number of apples in stock on that date, but I am unsure how to add an IF statement that checks to see if it is in stock, as well as being able to return an array instead of a single value.

CodePudding user response：

OFFSET is volatile and should be avoided if possible.

With TEXTJOIN we can do:

="["&TEXTJOIN(",",TRUE,FILTER(INDEX(E:G,MATCH(A9,D:D,0),0),INDEX(B2:B4,MATCH(E1:G1,A2:A4,0))="In Stock"))&"]"

CodePudding user response：

Try this:

We are going to use two FILTER invocations. First for rows (based on Ref. Date) and then for columns. In order to select the columns you can consider two options:

Manually as we did in K2:

 {1,0,1}

so we indicate with an 1x3 array the columns we want to consider.

Or calculated as we did in cell K6:

=FILTER(FILTER(E3:G6,D3:D6=I2),TRANSPOSE(IF(B2:B4="In Stock",1,0)))

The portion of the formula to build column selector is:

TRANSPOSE(IF(B2:B4="In Stock",1,0))

We evaluate the DataSet 1 to identify which fruit are in stock (the order of the DataSet 1 should be the same as the columns in DataSet 2). We need to build a column-wise array, so we TRANSPOSE. The result returns the same column-wise vector we put on first K2 cell manually, i.e.: {1,0,1}.

If you want exactly to show the output as follow: [1,7], then you can combine the previous analysis as it is on cell K10:

="["& TEXTJOIN(",", ,FILTER(FILTER(E3:G6,D3:D6=I2),
  TRANSPOSE(IF(B2:B4="In Stock",1,0)))) &"]"