How to see data everytime a value appears in multiple columns

I am usually pretty good at excel, but can't seem to figure out how to do this. Can someone itch my scratch by giving me the ah ha moment!?!?

I'm trying to show data every time a name appears in multiple columns. For example, raw data is:
Client Name1 Name2 Name3 Name4 $ Month WIN
Ford Andrew Carla Maria Jeff $25 April Yes
Coke Carla Andrew $30 May No
IBM Maria Andrew $20 June Yes


I want to see the client, $, month, & win for everytime "Andrew" appears in either Name1, Name2, Name3, or Name 4.

I have a pivot table built, but I can't reference "Name" to look up all four columns. (Name1, Name2, Name3, Name4)

Please!!

Hi and welcome to the forum

Assuming your data starts in A1 (client Name 1), try something like this...

=if(or(A$2="Andrew",B$2="Andrew",C$2="Andrew",D$2="Andrew"),E2,"")

You could of course put "Andrew" in its own cell and then just reference it, instead of hard-coding (prefered)

No, I assumed you had a regular table of data somewhere? That would either go in a column net to the data, or you could put it in a table somewhere else.

Im thinking that it may be easier to help if you uploaded a SAMPLE workbook?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.