Forum Discussion
Solved
Seeking help with IF/vlookup/maybe Filter? (corrected question)
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 | English, Computer Sci | Calculus, Spanish | Chess, Math | Robotics, Orchestra |
| Second HS | French, History | Physics, Computer Sci | Robotics, Art | Newspaper |
| Third HS | French, US History | Physics, World History | Band | Sculpture |
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 | English, Computer Sci | Calculus, Spanish | Chess, Math | Robotics, Orchestra | Yes | Yes |
| Second HS | French, History | Physics, Computer Sci | Robotics, Art | Newspaper | Yes | Yes |
| Third HS | French, US History | Physics, World History | Band | Sculpture | No | No |
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!
This is the formula in cell F2 in the example. The formula is copied down column F.
=IF(OR(ISNUMBER(SEARCH($F$1,B2)),ISNUMBER(SEARCH($F$1,C2))),"Yes","No")This is the formula in cell G2 in the example. The formula is copied down column G.
=IF(OR(ISNUMBER(SEARCH($G$1,D2)),ISNUMBER(SEARCH($G$1,E2))),"Yes","No")Enter the formulas with ctrl+shift+enter if you don't work with Office365 or 2021.
Now you can dynamically change the search criteria in cells F1 and G1. For example if you enter "Spanish" in cell F1 the formula returns "Yes", "No", "No" in range F2:F4.
5 Replies
This is the formula in cell F2 in the example. The formula is copied down column F.
=IF(OR(ISNUMBER(SEARCH($F$1,B2)),ISNUMBER(SEARCH($F$1,C2))),"Yes","No")This is the formula in cell G2 in the example. The formula is copied down column G.
=IF(OR(ISNUMBER(SEARCH($G$1,D2)),ISNUMBER(SEARCH($G$1,E2))),"Yes","No")Enter the formulas with ctrl+shift+enter if you don't work with Office365 or 2021.
Now you can dynamically change the search criteria in cells F1 and G1. For example if you enter "Spanish" in cell F1 the formula returns "Yes", "No", "No" in range F2:F4.
- Amazing!! Not only does it work, but I basically understand *why* it works, so I can (basically) explain it to other parents and my child, and other parents/kids can use it to do their own filtering. Thank you so much for taking the time to do this!!
This might work:
=BYROW(A2:E4,LAMBDA(row,IF(ISNUMBER(XMATCH("*"&"Computer Sci"&"*",row,2)),"Yes","")))Folded into a LAMBDA named 'Classes', it's user-friendly:
=Classes(A2:E4,"Computer Sci")- Thanks so much, I'll give that a try! And I've never heard of a lambda - I'll look that up.
- Shoot. I can't get it to work. I'll keep trying.
