title: Nest an Excel Array Function In a Non-Array Function and Return an Array
body: <p>I am trying to create a spreadsheet that will be used to provide quotes to customers.  Some part-numbers apply to a single item.  Some part-numbers are bundles of up to 4 items.  I am trying to create a formula that returns all of the values associated with a given part-number.</p>
  
  <p>Initially, I had two section in the quote - one that uses VLOOKUP to return part-numbers with single items and one that uses an array formula that returns an array of items.</p>
  
  <p>The first formula is</p>
  
  <p><code>=IF(ISNA(VLOOKUP(B12,PriceList,2,FALSE)),"",VLOOKUP(B12,PriceList,2,FALSE))</code></p>
  
  <p>The second is</p>
  
  <p><code>{=IFERROR(INDEX(Bundles!$B$2:$B$101, SMALL(IF($B$33=Bundles!$A$2:$A$101, ROW(Bundles!$B$2:$B$101 )  - 1,""), ROW() - 32 )),"")}</code></p>
  
  <p><a href="https://i.stack.imgur.com/2JDjd.png" rel="nofollow noreferrer">Screenshot showing results of first two formulas</a></p>
  
  <p>Both work fine on their own.  They rely on two data tables "PriceList" and "Bundles"</p>
  
  <p>I want the sales reps to be able to type a part-number in column B and get the correct part descriptions - whether it is 1, 2, 3, or 4 items - to display in column C.  I want them to be able to enter multiple part numbers on the same quote.</p>
  
  <p>I tried to base this on the part number</p>
  
  <p><code>=IF(LEFT(B29,4)="BUND",IFERROR(INDEX(Bundles!$B$2:$B$101,SMALL(IF($B$29=Bundles!$A$2:$A$101,ROW(Bundles!$B$2:$B$101)-1,""),ROW()-28)),""),VLOOKUP(B29,PriceList,2,FALSE))}</code></p>
  
  <p>This works for bundled items, but repeats single items.</p>
  
  <p>I would like to have a single data source (PriceList) and a single formula.  </p>
  
  <p><a href="https://i.stack.imgur.com/7v64A.png" rel="nofollow noreferrer">Partnumbers in Datasource</a></p>
  
  <p>What I am now trying to do is use COUNTIF.  For example if COUNTIF returns more than 1, use the array formula, else use the VLOOKUP formula.</p>
  
  <p>I picture it to be something like</p>
  
  <p><code>IF((COUNTIF(PriceList,Quote!B11)&gt;1),"BUNDLE",IF(ISNA(VLOOKUP(Quote!B11,PriceList,2,FALSE)),"",VLOOKUP(Quote!B11,PriceList,2,FALSE)))</code></p>
  
  <p>where "BUNDLE" is replaced by an array function.   I can't seem to come up with the right array formula.  </p>
  
  <p>I tried</p>
  
  <p><code>{=IF((COUNTIF(PriceList,Quote!B11)&gt;1),IFERROR(INDEX(Bundles!$B$2:$B$101, SMALL(IF($B$11=Bundles!$A$2:$A$101, ROW(Bundles!$B$2:$B$101 )  - 1,""), ROW() - 32 )),""),IF(ISNA(VLOOKUP(Quote!B11,PriceList,2,FALSE)),"",VLOOKUP(Quote!B11,PriceList,2,FALSE)))}</code></p>
  
  <p>This returns four rows of the same item for single items and nothing for bundles</p>
  
  <p>I had thought about placing the array function in another cell and referencing that cell, but this does not help if the bundle contains more than one item.</p>
  
  <p>Any thoughts or advice would be welcome.</p>