title: Excel Index Return multiple values matching multiple criteria
body: <p>Index and Match wont return multiple values need to find a way, whether thats a function or VBA. </p>
  
  <p>Essentially I have an Index and Match function that looks at two variables and then returns the corresponding Company that matches from a different sheet.</p>
  
  <p>i.e you select "Australia" from a drop down and then "GOLD" from another drop down then the Index and Match will pull a company from another sheet that operates in Australia and mines Gold.
  So far the function works but It only returns the first result and there may be more than one company and I haven't figured out how to return more than one result. </p>
  
  <p>The code so far is as follows:</p>
  
  <pre><code>=INDEX(Datapage!B$2:B$280,MATCH(1,(A$8=Datapage!$C$2:$C$280) * (B$8=Sheet11!$D$2:$D$280),0))
  </code></pre>
  
  <p>I have also tried this one:</p>
  
  <p>=IFERROR(INDEX(Table26[[Location]:[Commodity]],SMALL(IF(Table26[Location]=$B8,ROW(Table26[Commodity])-ROW($D$2)+1),COLUMNS($B14:B14))),"")
  the Data sheet has values in B2 - D280 </p>
  
  <p>But nothing happens and it only searches off one variable which is useless for this need. </p>
  
  <p>B being Company name</p>
  
  <p>C being Location </p>
  
  <p>D being Commodity </p>
  
  <p>I've attached some pictures for clarification </p>
  
  <p>this is what the sheets look like </p>
  
  <p><img src="https://i.stack.imgur.com/rPDJw.png" alt="Data page containing the names of companies"></p>
  
  <p><img src="https://i.stack.imgur.com/13h2v.png" alt="Search page"></p>
  
  <p>Any help would be greatly appreciated!
  Thanks </p>