Excel: Find the most common value in array given a certain value (IF, Index, Mode)

Question

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 Finance tell me the most common team number (Col B) of the users who access it:

The function I have so far is (obviously ignore the absolute cell references)

=INDEX($B$2:$B$20,MODE(MATCH($C$2:$C$20,$C$2:$C$20,0)))

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.

Here is an example of the table, a pivot and a peek at the formula and results.

Excel image with pivot

Any and all help is welcome. Cheers Matt

Answer 1

You might be looking for this formula:

=MODE.SNGL(IF(C1:C999="Finance",B1:B999))

CtrlShiftEnter

p.s. you can replace the hard-coded "Finance" with any cell reference.

---

EDIT

Indeed if there's only one matched row, or two rows with different values, the MODE will fail because there is actually no single mode. You want then to return the "first" match, therefore wrap the formula inside IFERROR with an INDEX/MATCH alternative:

=IFERROR(MODE.SNGL(IF(C1:C999="Finance",B1:B999)),INDEX(B:B,MATCH("Finance",C:C,0)))

CtrlShiftEnter