How to filter multiple columns for a specific value that may appear in any of them?

[u/Sam-Hinkie]

I'm working with a shared spreadsheet where columns A1-D1 are headers representing different roles within a team, and cells A2-D20 contain the names of team members. Since this spreadsheet is frequently filtered by different team members, it's easy to miss a name if it's not in the column being filtered. Is there an efficient way to filter the entire spreadsheet to find every instance of a specific name, regardless of which column it appears in?

Edit: I realized I did a bad job of explaining so I had to make a Sheet of it (I don't have Excel on my personal laptop, so completely forgot this was an option). Will look to see if the other comments can still apply, but adding an example to the comments.

Edit 2: Disregard the cell in the original post and refer to the updated comment, sorry about that. I realized that a Team Name column is important to mention

[u/AutoModerator]

/u/Sam-Hinkie - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/DeucesWild_at_yss]

=FILTER(C2:F5,(C2:C5=A2)+(D2:D5=A2)+(E2:E5=A2)+(F2:F5=A2))

Where C2 to F5 is the data you want to look through and the value to find is in A2 with headers in row 1

[u/Sam-Hinkie → u/DeucesWild_at_yss]

I think this puts me on track of what I'm looking for, but since I was unclear in my original post I added a comment explaining the situation better. Think this formula might still be involved in the formula needed after I clarified.

[u/Downtown-Economics26]

Excel 365 solution:

=VSTACK(HSTACK(A1:D1),IFERROR(HSTACK(IFERROR(FILTER(A2:A4,A2:A4=$G$1),""),IFERROR(FILTER(B2:B4,B2:B4=$G$1),""),IFERROR(FILTER(C2:C4,C2:C4=$G$1),""),IFERROR(FILTER(D2:D4,D2:D4=$G$1),"")),""))

https://preview.redd.it/37sw8v5b02md1.png?width=1739&format=png&auto=webp&s=c010141a6846c656555b7479b3e8917f26ec249e

[u/Sam-Hinkie → u/Downtown-Economics26]

Thanks I've updated the post to clarify myself, but this is still helpful in other areas. thanks!

[u/RotianQaNWX]

You can use simple countif, like for instance:

=COUNTIF(B10:E14, "*"&H10&"*")

Where those asterixes (*) means all marks that are there. Alternatively I would use filter + torow + index + count + find + isnumber (but this is much harder solution as you can imagine.

=LET(expr, FILTER(TOCOL(B10:E14), ISNUMBER(FIND($H$10, TOCOL(B10:E14))), "ERR"), 
     IF(INDEX(expr, 1)= "ERR", 0, COUNTA(expr))
)

If you do not wanna count, but write down you can do it like this (note, that it is expr part of earlier solution):

=FILTER(TOCOL(B10:E14), ISNUMBER(FIND($H$10, TOCOL(B10:E14)))

Here screenshot, alas in Polish:

https://preview.redd.it/272vzelaz1md1.png?width=1567&format=png&auto=webp&s=b8876eb80f304188a6cdf26f21b7c5fde0b54913

[u/Sam-Hinkie → u/RotianQaNWX]

Thanks! I've since updated to explain myself better with a picture attached. My concern is more for filtering for different text in a table set.

Out of curiosity what is the benefit of the second formula over the first one?

[u/RotianQaNWX → u/Sam-Hinkie]

First one counts items in a simple way, second in complicated. Second way of implementation works better with dynamic arrays than the first one.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
ADDRESS	Returns a reference as text to a single cell in a worksheet
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
COLUMN	Returns the column number of a reference
COUNTA	Counts how many values are in the list of arguments
COUNTIF	Counts the number of cells within a range that meet the given criteria
FILTER	Office 365+: Filters a range of data based on criteria you define
FIND	Finds one text value within another (case-sensitive)
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
INDEX	Uses an index to choose a value from a reference or array
ISNUMBER	Returns TRUE if the value is a number
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
OR	Returns TRUE if any argument is TRUE
ROW	Returns the row number of a reference
SEARCH	Finds one text value within another (not case-sensitive)
TEXTJOIN	2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TOCOL	Office 365+: Returns the array in a single column
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
21 acronyms in this thread; the most compressed thread commented on today has 24 acronyms.
[Thread #36664 for this sub, first seen 31st Aug 2024, 19:46] [FAQ] [Full list] [Contact] [Source code]}

[u/Shiba_Take]

Do you maybe want this?

=TEXTJOIN("; ", TRUE, A2:D2)

https://preview.redd.it/nwnrijy632md1.png?width=1258&format=png&auto=webp&s=8c1587b89d014f8b657f94690ce2fa23a69c8c4f

[u/Shiba_Take → u/Shiba_Take]

https://preview.redd.it/embfe1zt32md1.png?width=1157&format=png&auto=webp&s=66363122ffd69e36ee9c2725fb2d20125b8e4f00

You could also use Power Query to unpivot columns and then use the Name column to filter (the new table, not the original)

[u/Sam-Hinkie → u/Shiba_Take]

This helpful but was hoping to avoid having to type in the name each time

[u/Shiba_Take → u/Sam-Hinkie]

Maybe like this?

https://preview.redd.it/89spk1kyg2md1.png?width=2172&format=png&auto=webp&s=4e678fc139a4c0de440760778ec73a6eb29a45b3

Used Format as Table on the leftmost table to turn it into Excel Table named Table1 (default name, can be renamed in Table Design tab).

Under Names (H1) got a unique list of names (which you already seem to have elsewhere).

H2:

=UNIQUE(TOCOL(Table1[[Role 1]:[Role 4]]))

Under Name (I1) you select the desired name (used Data Validation on I2, Allow: list, Source =H3#

K1:

=Table1[#Headers]

K2:

=FILTER(Table1, BYROW(Table1[[Role 1]:[Role 4]], LAMBDA(row, OR(row=I2))))

[u/Sam-Hinkie → u/Shiba_Take]

This will probably be my best bet. Thank you

[u/Sam-Hinkie → u/Shiba_Take]

Solution verified

[u/reputatorbot → u/Sam-Hinkie]

You have awarded 1 point to Shiba_Take.

---

^{I am a bot - please contact the mods with any questions}

[u/Sam-Hinkie]

https://preview.redd.it/jkw7o7a552md1.png?width=1838&format=png&auto=webp&s=429bd45d2d4b29eac51831c5050889630bcc37ff

Would like to easily search for a person on all the different teams that they are on, while also being able to still see all other columns .

[u/FreeXFall → u/Sam-Hinkie]

Maybe look at conditional formatting? So add a cell where type in a name, and if anything in the table matches the name the color changes.

With that (or almost all excel formulas) - it can be tricky as names have to match exactly.

Other option - flip your table. List name alphabetically, and have teams as columns. Just put an X in they’re on that team. Name are now only listed once and you find it by just looking alphabetically.

[u/Sam-Hinkie → u/FreeXFall]

And to your second point that would be a good option too, but the actual sheet has like 20 columns and like 500 rows, so that would be to much of a difference for my situation Didn’t realize how much more detail to my situation needed to be explained. Good response for what I provided though!

[u/Sam-Hinkie → u/FreeXFall]

What if the names are from a drop down list on another sheet?

[u/valherquin → u/Sam-Hinkie]

You could use FILTER to create a "duplicate" of the table but only with the rows you want. Like it could be in another tab to search for the people, on top you have a cell with idk a drop-down menu with all people and the filter will search for that value in columns B to E and display the whole table. Is it something like that what you want? That you'll see the whole table but only rows 2-7 if you are searching for bob?

[u/Dismal-Party-4844]

If you want to return a comma separated list of matching cell addresses, irrespective of if filtering is enabled:

Search term is "Alice" or stored in a helper cell

=TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH("Alice", A2:D20)), ADDRESS(ROW(A2:D20), COLUMN(A2:D20)), ""))

https://preview.redd.it/g7qm4i3gb2md1.png?width=823&format=png&auto=webp&s=6d5bff15306794cccf12477632d9723c6ed52789

[u/LegWise7843]

Seeing our names acknowledged across the board means we’re not just part of a team; we’re seen, valued, and remembered. Strip away the columns, and let’s not miss a single one of us.