Forum Discussion

Paul Danaher's avatar
Paul Danaher
Copper Contributor
May 20, 2018
Solved

Returning list from 3D array

I have a weekly (Saturday) market and 40 vendors. I have a matrix with months as the rows and Saturday dates as the columns (1-5). I ask vendors to check the dates they want to be there. I create wor...
Formulas & Functions
functions
  • Matt Mickle's avatar
    Matt Mickle
    Jun 08, 2018

    Paul-

     

    Just wanted to check back in and see if you were able to resolve your issue with the sample file provided?

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
May 21, 2018

Paul-

 

I think this is a better setup. 

 

Try these modifications:

 

1. Have one worksheet for all vendors

2. Have a Vendor Dropdown

3. Have a month Drop Down

4. Have a weekend Dropdown

5. Have a Query Worksheet that will tell you, who will perform or be in attendance on X date.

 

I've set it up where the dates of the Saturday's will narrow down to a specific month when the month is selected.  i.e. If you select February it will only show Saturday's in February.  If you select March it will only show Saturday's in March.

 

Use an array formula to help identify the vendors for a particular Saturday:

={IFERROR(INDEX(Data!$A$2:$C$100,SMALL(IF(Data!$C$2:$C$100=Criteria,ROW(Data!$A$2:$C$100)-1),ROW(2:2)),COLUMN()),"")}

 

 

 

Please see the attached file for an example

 

 

 

Paul Danaher's avatar
Paul Danaher
Copper Contributor
May 21, 2018
A quick question: why doesn’t vendor 1 show up in the list for 2018-01-06? The Query seems as if it should return them as well.
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    May 21, 2018

    Paul-

     

    I went ahead and updated the formula.  There was a reference issue that was preventing the formula from pulling in Vendor 1.  It should work without issue now.  I used 1/6 and 1/20 as test cases.

     

     

    • Paul Danaher's avatar
      Paul Danaher
      Copper Contributor
      May 21, 2018
      Thank you - I’ll get my second cup of coffee and look at the change in the query - I used mostly nested functions back in the day, and I need a bit of time to get my head around the new stuff.
      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        May 21, 2018

        Paul-

         

        Here are a few resources to help learn about array formulas.  The most important thing is that you enter them using the key combination CTRL + SHIFT + ENTER.  It may also help to go to the formulas tab and use the evaluate formula option:

         

        https://support.office.com/en-us/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

         

        https://www.ablebits.com/office-addins-blog/2015/02/25/array-formulas-functions-excel/

Resources