1

I have a sheet of students and the classroom numbers that they visit throughout the day. I am trying to use a filter formula that returns the name of any student in a specified classroom. However, the classrooms are alphanumeric (eg: A-10, A-11, A-12).

I am using the below formula. When the room numbers are just numbers, the filter works and returns the students name, but when there is a letter in the room number, it is returning this error: "No matches are found in FILTER evaluation."

Column A contains student names, Columns C-J contain the classrooms that student attends and R3 is an formula the pulls up the desired room number.

=FILTER($A6:$A,($C6:$C=R3)+($D6:$D=R3)+($E6:$E=R3)+($F6:$F=R3)+($G6:$G=R3)+($H6:$H=R3)+($I6:$I=R3)+($J6:$J=R3)))

Classrooms

1
  • Welcome to Web Applications. The image has merged cells and doesn't include the row headers so it's unclear if the formulas was properly set. Please try to reproduce the problem in a smaller area and update the image an formulas incluiding all the relevant details. Also it might be helpful if you include the data as text rather as an image and/or share a spreadsheet to make other reproduce the problem. Apr 26, 2022 at 0:02

1 Answer 1

0

The filter() formula you show seems fine. Since it cannot find any results, chances are that the values in R3:3 do not match the values in C6:J.

Row numbers are not shown, so it is possible that the correct range is actually R2:2. Try this formula in a free cell:

="R3: " & R3, ", C6: " & C6, ", match? " & R3 = C6

Even if the values look the same, they can still differ because of trailing spaces and the like. Check the formula you have in R3.

4
  • Thanks for the reply! The formula you gave is also returning an error. I have attached row numbers. The formula in R3 is: =SPLIT(ArrayFormula(JOIN(" , , ,",FILTER(O6:O,P6:P>2))),",") I have updated the screen shot to show row numbers as well as the reference for the R3 formula Apr 26, 2022 at 21:19
  • As suspected, there are trailing spaces in the values in R3:3. The problem is with the join() delimiter that includes a space. Remove the space to make it work. Consider sharing a publicly editable sample spreadsheet. Apr 27, 2022 at 4:54
  • That did the trick! Thanks so much Apr 27, 2022 at 20:06
  • I gather that this is your first question at Stack Exchange. See What should I do when someone answers my question? Apr 27, 2022 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.