I use Google Sheets in my classroom a lot. I used to rely on combinations of the vlookup, importrange, and index/match functions to get information, but I’ve recently switched to using if and filter to return arrays of information from master spreadsheets.
Using filter is nice because it takes multiple conditions and you can set which columns of the array you want to return for your summary sheet. A pretty standard search looks like this:
=if(filter($A$2:$A,$A$2:$A=$F$3)=$F$3,filter($B$2:$B,A2:A=$F$3),"")
Here’s a sample spreadsheet so you can see how the result is returned after changing the filter term.
So, let’s break it down:
Cell F3 holds my search term, “A”, “B”, or “C”.
(filter($A$2:$A,$A$2:$A=$F$3) – Filter looks through a range of cells ($A$2:$A) for a specific condition ($A$2:$A=F3), much like the IF statement. The exception is that this only returns the matching content rather than a boolean (true/false). The filter, in this case, is serving as the boolean check for the IF statement it’s wrapped within.
=If() – This function is super helpful because it limits what happens in the sheet. It’s like conditional formatting, but for your functions. It takes two arguments, minimum, but you can set up to three: the condition to check, what to do if true, and what to do if false. In this case, the conditional is set with the filter function (see above). If the filter returns a cell with an “A” in it, the TRUE condition is run.
filter($B$2:$B,A2:A=$F$3) – If it’s true, I want a different column returned. In this case, it’s the names of students with group “A” set. Filter works the same way, except this time, it searches for column B (the names) that match the search parameter (“A” in column A).
In other words, the function reads like this:
- If
– filter through column A
– Look for cells that contain “A”
– If an “A” is found, the IF statement is TRUE
- Execute the “TRUE” parameter
– Print the student’s name in the cell
- If not, leave a blank cell
I added a third column, which prints the student’s project content just to show how these functions can be used in conjunction with one another.
I know you can use ARRAYFORMULA to do essentially the same task, but using ARRAYFORMULA doesn’t allow you to add custom content in the column – the throws an error saying data cannot be overwritten. I don’t run into that case often, but it’s often enough to be annoying.
Again, this is difficult to see without checking out the example spreadsheet. It’ll take some playing, but once you get it, it’s very helpful. Leave a note in the comments if you get stuck and need some help working it out.