How to get rows that contain specific value or range of values

Copper Contributor

Hi friends, 

Every time I ask a question in this community I receive a quick and brilliant answer, this time won't be different. 

I'm trying to get rows from a table with a range that contains specific values or range of values, I have a table of schools with two columns that have high and low grades for each school, I want to filter the table to keep schools that have either 9, 10, 11, 12 grades.  

For example, a school that has grades 1 - 9 should be included because there's a 9th grade in it. 

A school that has grades 2 - 8 shouldn't be included because we don't have either of the above grades.  A school that teach grades 10 - 12 should be included because it has 10 grade, also 11 and 12. 

How to achieve this in excel? 
I included a screenshot of the original table and how I want the result to be. 
Thanks in advance for your help. 

 

 

2 Replies

@haitham From your question it seems you are just trying to filter schools grade higher or equal 9. Try FILTER() function. See attached file.

=FILTER(A2:D7,D2:D7>=9)

Harun24HR_0-1663142264926.png

 

 

Thank you!
What if I want schools that have the 9th grade, the same formula applied to High grade column doesn't seem to work. I tied to work out a FILTER with AND but it didn't return any results.
Would appreciate a help?