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

Occasional Contributor

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

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 name AP classes, pt 1 AP classes, pt 2 Extracurriculars pt 1 Extracurriculars, 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 name AP classes, pt 1 AP classes, pt 2 Extracurriculars pt 1 Extracurriculars, pat 2 AP 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

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

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

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

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

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?

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

Ugh that looks awful and is incomprehensible, sorry. I'll post a new question with a chart that makes sense. Sorry about that!