Display Results Matching Search Criteria, Ignore Blanks

[dramqueenuk]

I have a sheet called "Data" that contains many rows of information in columns A to K.

I have a sheet called "Search" where I have this:

The user can fill in the search fields (as many or as few as they want). These fields are named:
C2 = "Agency"
C4 = "User_ID"
C6 = "Surname"
E6 = "First_Name"
F2 = "From_Date"
F3 = "To_Date"

Ultimately, I want a search to be done when any of these fields are populated, searching the data in the 'Data' sheet and displaying ALL matching rows.

I was going to use a helper cell in H2 to do a countif so that in cell B9, my formula could check if this number of rows was already displayed before running the rest of the formula as I'd read that would speed it up. However, I was finding that I was having to use a lot of nested IF conditions in cell H2 to tell it to ignore blanks; so many in fact that it told me I had too many arguments, and I feel there must be a better, more efficient way of doing this.

I can't use macros as the users will be viewing the spreadsheet online, not in the desktop version of Excel.

Any suggestions of a better method?

 

[AhoyNC]

Maybe this link will help.

 

[dramqueenuk]

That's exactly what I needed, AhoyNC. Thanks very much for taking the time to share it with me!