Retrieving Full Rows For Any Cells Matching Search Query

OliverScheurich PeterBartholomew1 SergeiBaklan Thanks for the replies! It looks like FILTER mixed with smart use of Tables is going to be the way to go, and I've been learning a lot through the examples shown here & messing around in a more complex test document to make sure the function works for as many use cases as possible.

I'm still having issues attaching files on this site, will try to figure it out to provide the document I'm messing with because I think it will make it very clear what my aim is, but I wanted to give an update on the formula I have been working with and describing how it's working for anyone who might stumble upon this discussion.

The first thing I did was create a table out of a larger set of data that my first example, designating columns for date, first name, last name, email, phone number, product purchased, and call details which contain duplicate entries in every column.

I then used FILTER to reference each column of the table individually against a single cell (in my document it's cell E3), like such:

=FILTER(Table,
(Table[Column1]=E3)+
(Table[Column2]=E3)+
(Table[Column3]=E3),"")

This accomplishes most of what I'm looking for! I am still learning the Excel syntax and operators, the plus sign (+) designates "OR" so Excel is looking at my query and thinking "does it match Column 1 or Column 2 or Column 3 or etc?" which is the kind of designation I was looking for, since a lot of functions seem to only work with single rows or columns. With this, rows will be pulled if any cell in any column is an exact match.

Product Search:

First Name Search:

Date Search:

With this setup, the only roadblocks I am encountering are that I can't get partial matches to work (tried ISNUMBER and SEARCH but kept getting popup errors), I can't the list of #N/As that you can see above to remain blank (IFERROR, IFNA, ISERROR, etc don't get rid of them/give me popup errors), and queries with a single result will repeat down the list:

Some of these might just be the limitations of this kind of software/logic, in which case this is still functional for my needs and I can either adjust what I'm trying to do to accommodate or just live with it haha. I got further than I expected with the great help here! When I get home I will try to upload the file again, might be an issue with the network at work or I might be missing something but I can't find another way to attach a file aside from drag & drop which isn't working on this machine.

Thanks again!

ChazS 

As OliverScheurich suggests, with Excel 365, using FILTER is the only sensible option.

ChazS 

=INDEX(A$2:A$10,SMALL(IF($C$2:$C$10=$B$12,ROW($A$2:$A$10)),ROW(1:1))-1)

Is this what you want to do? As you work with Office365 you can as well use FILTER formula.