Share via

Using or (+) instead of and (*) in excel FILTER function

Anonymous
2025-04-18T05:38:54+00:00

I have a data set that has one cell that could contain multiple data. In most situations I would be only selecting one item but there is one time when I want to select cells that contain one item OR another.

The data that could be in this cell is: "Optional" "Critical" "Required" "Input". I want to be able to select cells singly based on a entered cell except in one case. That requires that I see all cells with both "Critical" or "Required" at one time.

I have everything working except the "Critical" or "Required" using this formula.

((ISNUMBER(SEARCH(LEFT(Selection_Transaction_Type,2),Transactions!Transaction_Type)+(ISNUMBER(SEARCH(RIGHT(Selection_Transaction_Type,2),Transactions!Transaction_Type)) where Transactions!transaction_type is my data and Selection_Transaction_Type is my selection cell.

Everything works fine but the "OR". The Selection cell contains "CrRe" which I thought should give me all cells with either "Critical" or "Required" but it doesn't.

Thanks,

TheOldPuterMan

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2025-04-18T09:07:45+00:00

    F2: =FILTER(A2:B9,(LEFT(B2:B9,2)=LEFT(D2,2))+(LEFT(B2:B9,2)=RIGHT(D2,2)))

    1 person found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2025-04-18T08:18:23+00:00

    Something like

    =FILTER(range, ISNUMBER(SEARCH(LEFT(Selection_Transaction_Type, 2), Transaction_Type))+ISNUMBER(SEARCH(RIGHT(Selection_Transaction_Type, 2), Transaction_Type)), "")

    (Perhaps use Transactions!Transaction_Type but I don't know if that is really necessary)

    1 person found this answer helpful.
    0 comments
  3. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2025-04-18T23:24:27+00:00

    Hi,

    Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.

    0 comments