Google Sheets INDEX/MATCH with multiple results

Question

test spreadsheet

Tab 'Raw Data' contains a combination of "attendance sheets" from multiple activities. Students are in multiple activities on the same Date.

Tab 'Result' lists all unique Students and Dates.

Goal: 'Result'!B3:D11 marks P or A if that student was P for any of the activities on that Date.

For example: On 12/5, Evan was marked A, A, and P in his activities - so he should be marked P for that Date.

If these were numbers, I think I'd be able to use SMALL or LARGE in combination with INDEX/MATCH to count the results...but I'm unsure how to make this formula work with A's and P's.

Answer 1

Since in your sample sheet you do not use a formula for Unique students I suggest -in cell F3- you use

=UNIQUE(FILTER($A$2:$A$33,B2:B33<>""))

We can now use the above formula as part of the following one in cell G2 so as to get the desired result

=ArrayFormula({B1;IF(COUNTIFS($A$2:$A,"="&UNIQUE(FILTER($A$2:$A,B2:B<>"")),B2:B,"=P")>0,"P","A")})

Drag the above to adjacent cells to the right, to get the rest of the columns.

(Please adjust ranges to your needs)

Functions used:

• ArrayFormula
• IF
• COUNTIFS
• UNIQUE
• FILTER

Answer 2

@Gabe, I've added a sheet ("Erik Help") to your sample spreadsheet. There are three formulas in it.

In A2:

=UNIQUE(FILTER('Raw Data'!A2:A,'Raw Data'!B2:B<>""))

This just generates a list of unique names of students from 'Raw Data'.

In B1:

=FILTER('Raw Data'!B1:1,ISNUMBER('Raw Data'!B1:1))

This generates a list of all dates from the first row of 'Raw Data'.

In B2:

=ArrayFormula(IF(NOT(ISERROR(VLOOKUP(FILTER(FILTER(A2:A&B1:1&"P",A2:A<>""),ISNUMBER(B1:1)),FLATTEN(FILTER(FILTER('Raw Data'!A2:A&'Raw Data'!B1:1&INDIRECT("Raw Data!B2:"&ROWS('Raw Data'!A:A)),'Raw Data'!A2:A<>""),ISNUMBER('Raw Data'!B1:1))),1,FALSE))),"P","A"))

As to how this longer formula works, while I usually leave detailed explanations, I'm honestly just too tired to do the full shebang at the moment. But I'll explain the basics.

You'll notice two occurrences of FILTER(FILTER(...)). The first is just making sure to keep the formula focused only on cells in each sheet that actually have data. This will help speed up processing.

IF(NOT(ISERROR(VLOOKUP(...)))) breaks down to "If you can find THIS... do X."

What we're looking for is every string possible made from every name in the results A2:A concatenated with every data from B1:1 concatenated with "P"; virtually (i.e., in memory), these results will fill the 2D grid in results with such concatenations (e.g., B2 in the grid will store "Jon44170P", C3 will store "Barbara44171P", etc.).

Each of these strings will be VLOOKUP'ed in a FLATTENed virtual column made up of every name in 'Raw Data' concatenated with every data in 'Raw Data' concatenated with the actual 'P' or 'A' in the rest of the 'Raw Data' grid.

If the VLOOKUP is successful (i.e., IF(NOT(ISERROR(VLOOKUP(...))))), then a "P" was found for that name on that date. If it wasn't found, then the default "A" is assigned.

Because these formulas are self-expanding, you can add data to 'Raw Data' as you like without needing to drag formulas anywhere.

I also added conditional formatting to expand or contract the background fill color as data expands or contracts.

Answer 3

try:

=ARRAYFORMULA(QUERY(REGEXREPLACE(SORTN(SORT(UNIQUE(SPLIT(FLATTEN(
 IF('Raw Data'!B2:D="",, 'Raw Data'!B1:D1&"♂"&TEXT('Raw Data'!B1:D1, "m/d")&
 "♀"&'Raw Data'!B1:D1&"♂"&'Raw Data'!A2:A&"♀"&'Raw Data'!B2:D)), "♀")), 3, 0, 2, 1), 
 9^9, 2, 2, 1), "(\d+♂)", ), 
 "select Col2,max(Col3)
  where not Col1 matches '#VALUE!' 
  group by Col2 
  pivot Col1"))