Can "Advanced Filter" tool search for a person's name across MULTIPLE columns?

I have an Excel index to organize information for a collection of photographs. See the attached sample worksheet.

Because one photo may have more than one person in it, I have created multiple columns for their names (Person1, Person2, Person3, etc.)

I want to be able to filter the spreadsheet to display only those rows that contain the name of a particular person. My problem is that the person's name may be located across MULTIPLE possible columns (Person1, Person2, Person3, etc.).

Can the "Advanced Filter" feature be successfully be used for this? If not, is there another way to accomplish this?

APPRECIATE IT!!

Here's a simple way of achieving that using a couple of formulae. First of all, I've added a new sheet and used B1 in the new sheet to allow you to enter a name. This must be a full name, exactly as entered in the other sheet.

Then I have used column K on Sheet1 as a helper column, with this formula in K2:

=IF(COUNTIF(E2:I2,Sheet2!$B$1),MAX(K$1:K1)+1,"-")

Copy this down to the bottom of your data (or beyond, if you intend to add more data in the future). This formula identifies those records which match the criteria, i.e. the name being searched for exists in one of the columns E to I.

Then you can use this formula in A4 of Sheet2:

=IF(ROWS($1:1)>MAX(Sheet1!$K:$K),"",INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$K:$K,0)))

and this can be copied across to D4, and then those formulae copied down as far as you think you may need them (I've copied to row 15 in the example file).

Change the name in B1 (the yellow cell) and the display will automatically update.

Hope this helps.

Pete

This is a BRILLIANT solution! And it seems to be much quicker than the "Advanced Filters" that I was attempting. THANK YOU very, very much!


P.S. I am sorry for the "double post," but I am new and did not notice the "Formulas and Functions" category until I had already posted in "General."

Most of us read threads from all the sub-forums, so you don't need to be too concerned with where it goes, although you can always ask a Moderator to move a thread into a different sub-forum for you.

Glad you like the solution - if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

In attachment using advanced filter. For your convenience, I have added a list of names and an event procedure.

You are probably aware that someday you will come across a photo with more people than the defined columns and that the person you are looking for is not the one in the photo (duplicate names).

Artik