Show no results if search box is empty using Filter Function on Google Sheets

user_15293087 0
Viewed 114

I'm hoping somebody has a quick solution for me. I have a fantasy sport league setup on a google sheet. One of the sheets allows other people to search a database of players. Its broken down into four different search boxs offering the following search options: By Player, Position, Owner or Player designation. I'm using the filter function:

=filter(Sheet242!A2:E,search(B6,Sheet242!A2:A),search(E6,Sheet242!E2:E),search(B8,Sheet242!C2:C),search(E8,Sheet242!B2:B))

It draws from a table on a separate sheet.

This works great with one exception. It returns all the values in my table when all 4 search boxes have no value. I want it to filter no results if all 4 boxes are empty.

Here is a link to my google sheet so you can have a better visual of what im trying to do: https://docs.google.com/spreadsheets/d/1XlfWyDOi-cEynVCcnWEpbOF7qIm_HlAKk2yG31rYvJw/edit?usp=sharing

The two sheets in question are "Player Database" and "Sheet 242".

Any suggestions would be greatly appreciated!

google-sheets if-statement filter search function
1 Answers

try:

=IF(LEN(B6&B8&E6&E8)=0,, FILTER(Sheet242!A2:E,
 SEARCH(B6, Sheet242!A2:A), SEARCH(E6, Sheet242!E2:E),
 SEARCH(B8, Sheet242!C2:C), SEARCH(E8, Sheet242!B2:B)))

enter image description here

user_56326290
Related