I have two sheets. Rows are unique in both sheets.
Sheet1, column A = exhaustive list of domains (e.g. 'madeupdomain1.com') Sheet2, column B = exhaustive list of URLs (e.g. 'madeupdomain1.com/article1', 'madeupdomain1.com/article2', 'madeupdomain1.com/article3', ...) Sheet2, column C = lists an author (e.g. 'Alice', 'Bob', 'Charlie', ...). Authors appear multiple times.
How do I return the most frequent author for a given domain?
I.e. how do I find the most common text value, filtered by some other value?
Sheet2:
| Domain | URL | Author |
|---|---|---|
| madeupdomain1.com | madeupdomain1.com/article1 | Alice |
| madeupdomain1.com | madeupdomain1.com/article2 | Bob |
| madeupdomain1.com | madeupdomain1.com/article3 | Charlie |
| madeupdomain2.com | madeupdomain2.com/article1 | Alice |
| madeupdomain2.com | madeupdomain2.com/article2 | Bob |
| madeupdomain2.com | madeupdomain2.com/article3 | Alice |
| madeupdomain3.com | madeupdomain3.com/article1 | Alice |
| madeupdomain3.com | madeupdomain3.com/article2 | Charlie |
| madeupdomain3.com | madeupdomain3.com/article3 | Bob |
I have tried:
=INDEX(MODE.MULT(IFNA(MATCH(FILTER(Sheet2!$C$2:$C, Sheet2!$B$2:$B=Sheet1!$A2),UNIQUE(FILTER(Sheet2!$C$2:$C, Sheet2!$B$2:$B=Sheet1!A2)),0))))
So I was expecting for 'madeupdomain2.com' to see 'Alice' returned, but I just get a numerical value (which I guess is caused by using MODE?). Urgh.
Any help much appreciated!