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

1
votes

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!

2
google-sheetsmatchgoogle-sheets-formulaarray-formulas
I don't get why "=MATCH(B2,C2:E2,0)" shouldn't work? It will return the position of the Reviewer, which would correspond to the column 1-3. – Wizhi
stackoverflow.com/questions/16140642/… – BigBen
I have an answer for this, but it is more convoluted than i think it would be to return the actual data from the bound matrix sch as allocated hours. That is, i think the solution to the NEXT step is simpler than this intermediary step. Tough to tell without a sample sheet. Share an editable sheet to demo the problem (including the "next step") and i think i can help – MattKing
@Wizhi the MATCH formula does not work within an arrayformula as the range can only be a single row or column. It does not iterate through "C2:E2" like you would expect from an ArrayFormula – Paul M
@ MattKing linked is a workbook with example data tables docs.google.com/spreadsheets/d/… – Paul M

2 Answers

0
votes

The best I've been able to come up with so far is this SWITCH statement. It works but not so elegant

=ArrayFormula(SWITCH(Current_Assignee, INDEX(Queue,,1), "1", INDEX(Queue,,2), "2", INDEX(Queue,,3), "3", INDEX(Queue,,4), "4", INDEX(Queue,,5), "5"))
0
votes

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.

enter image description here

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.