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 a...

excel

Formulas and Functions

OliverScheurich
Oct 05, 2022
flourish11747
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.

OliverScheurich

Gold Contributor

Oct 05, 2022

flourish11747

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.

flourish11747
Copper Contributor
Oct 05, 2022
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!!

Forum Discussion

Seeking help with IF/vlookup/maybe Filter? (corrected question)

Resources