title: Excel: Find the most common value in array given a certain value (IF, Index, Mode)
body: <p>I'm trying to find the most common value in a range given a previuous value occurs, so for instance, if Shared mailbox (Col C) is equal to <em>Finance</em> tell me the most common <em>team number</em> (Col B) of the users who access it:</p>
  
  <p>The function I have so far is (obviously ignore the absolute cell references)</p>
  
  <pre><code>=INDEX($B$2:$B$20,MODE(MATCH($C$2:$C$20,$C$2:$C$20,0)))
  </code></pre>
  
  <p>I could do this with a pivot but I need to insert the value into a cell and vlookup in a pivot wont cut it. Also there are tens of thousands of rows. But essentially the pivot result is what I'm looking to get to, just in a cell via a formula.</p>
  
  <p>Here is an example of the table, a pivot and a peek at the formula and results.</p>
  
  <p><a href="https://i.stack.imgur.com/wasMR.png" rel="nofollow noreferrer">Excel image with pivot</a></p>
  
  <p>Any and all help is welcome.
  Cheers
  Matt</p>