Google Sheets ArrayFormula that returns an index of the column that matched specified criteria for each row

Question

I've been searching for several hours for what I thought would be a pretty straight forward problem but without any luck.

I need an array formula (needs to calculate for range without copying down the formula) that returns an index reference to the column containing a match for the passed criteria for each row. I don't need the value returned, which is what I've seen related problems solving for, just the column index. I will be using the returned index value to pull data from a bound matrix containing data such as allocated hours. I tried to use MATCH inside an ArrayFormula with a dynamic index for the lookup range but it doesn't increment the row as I would expect. Below is example data with the desired results shown in the first column (technically the results will be returned in a separate worksheet but included here for illustrative purposes), assignee is the criteria for which to find the matching column index across reviewers 1 - 3.

   
   +---------+----------+------------+------------+------------+
   | Results | Assignee | Reviewer 1 | Reviewer 2 | Reviewer 3 |
   +---------+----------+------------+------------+------------+
   |       2 | Paul     | Tim        | Paul       | Sue        |
   |       1 | Nick     | Nick       | Linda      | Adam       |
   |       3 | Bill     | Ryan       | Paul       | Bill       |
   |       2 | Tom      | Paul       | Tom        | Sarah      |
   +---------+----------+------------+------------+------------+

I've been struggling with this for a while so any guidance would be appreciated!

Answer 1

Try this:

=MMULT(ARRAYFORMULA(--('Table 2'!A3:D7) * --('Table 1'!A3:A7 = 'Table 1'!B3:E7)), SEQUENCE(COLUMNS('Table 1'!B3:E7), 1, 1, 0))

1. --('Table 2'!A3:D7) - places 0s instead of blanks in table 2 (needed for MMULT).
2. --('Table 1'!A3:A7 = 'Table 1'!B3:E7) - gives a table with 1s in cells corresponding to current reviewer, and 0s in all the other.
3. Then those two ranges are multiplied cell by cell. That gives a table with the right hours in cells with the reviewers' names, one value in a row.
4. MMULT gives a row wise sum, which is effectively a column of those hours from the previous step.

If you'll have a bigger table you'll just need to adjust Table 1'!A3:A7, 'Table 1'!B3:E7, and Table 2'!A3:D7 accordingly. The rest will remain the same.