Seeking help w/IF statement (or maybe Vlookup/Xlookup?)

Occasional Contributor

Hi,

I'm still pretty much a newbie when it comes to Excel, and I wonder if I could ask for help with something. I have a list of several hundred high schools in our area, with multiple columns for academics and extras, and I'd like to find the ones that have AP computer science classes and also robotics clubs, since that's what my son is interested in (he's in 8th grade and applying to HS).

 

A smaller, simplified version of the spreadsheet would look like this:

 

HS nameAP classes, pt 1AP classes, pt 2Extracurriculars pt 1Extracurriculars, pat 2
First HS    
Second HS    
etc.    

 

I'd like to come up with a formula that could search multiple columns of AP classes to say Yes/No (or 1/0) to indicate whether Comp Sci is offered, and a different formula that would do the same for robotics clubs:

 

HS nameAP classes, pt 1AP classes, pt 2Extracurriculars pt 1Extracurriculars, pat 2AP Comp Sci?Robotics?
First HS      
Second HS      
etc.      

 

Can anyone offer any guidance about whether an IF statement or Vlookup/Xlookup or another strategy would be best, and what might be a good formula to use? Extra points if it's easy so other parents can use it to search for chess or Spanish or what have you.

 

Thank you!

3 Replies

@flourish11747 

=FILTER($A$2:$A$18,B2:B18=1)

You can try the FILTER function if you work with Office365 or 2021 or Excel online.

hs name.JPG

Thanks so much for the speedy reply! I realized that I should have been more clear - the spreadsheet looks like this, but with many more columns and much more text:

HS name AP classes, pt 1 AP classes, pt 2 Extracurriculars pt 1 Extracurriculars, pat 2
First HS Eng, Comp Sci Calc, Span Chess, Math Robotics, Orch
2nd HS Fre, Hist Phys, Comp Sci Robotics, Art News

....for some reason I couldn't paste the chart in, so I recreated it as best I could. I'm hoping to be able to do this: for HS 1, search multiple columns for "Computer Science" and indicate in a separate column whether that subject is listed; and the same for robotics under extracurriculars, etc. Sorry for not being more clear about that! Is that possible?
Ugh that looks awful and is incomprehensible, sorry. I'll post a new question with a chart that makes sense. Sorry about that!