Find Rows Where Either of Two Excel Columns Contains a Value
Question details
Find every row where either of two columns matches a specified text value.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has department values in two columns, and the user wants to identify rows containing a selected department such as Finance.
- Observed behavior
- Matching rows can be identified with a helper formula and displayed using a filter.
Check which columns contain the values to search and identify an empty helper column. The examples use columns A and B, with headers in row 1 and data starting in row 2.
Use an OR helper formula to find exact matches
Use this method when a cell must equal the search value, such as Finance, rather than merely contain it as part of longer text.
Type Finance in cell P1. You can change this cell later to search for another department.
Enter a header such as Match in C1, then enter =OR(A2=$P$1,B2=$P$1) in C2.
Drag the fill handle from C2 down through the last row of data. The formula returns TRUE when either cell in the row equals the value in P1.
Select the data range, choose Data > Filter, and use the filter arrow in the Match column to show only TRUE.

Find partial text matches in either column
Use SEARCH when the target may appear within longer cell text, such as Finance Team.
Filter Rows Across Two Columns in WPS Spreadsheets
Use an OR helper formula in WPS Spreadsheets to identify rows where either column matches a department, then filter the results.
- 1. Open the spreadsheet: Open your workbook in WPS Spreadsheets and locate the two columns to search.
- 2. Set the search value: Enter Finance or another department in P1.
- 3. Create the helper column: Enter Match in C1 and =OR(A2=$P$1,B2=$P$1) in C2, then drag the fill handle down.
- 4. Filter the results: Select the data range, choose Data > Filter, and use the Match column's filter arrow to display TRUE rows.

Frequently Asked Questions
How do I change the department without editing the formula?
Put the department in P1 and use =OR(A2=$P$1,B2=$P$1). Change P1 to update the results for all rows.
Does the OR formula find Finance inside Finance Team?
No. The equality formula checks the whole cell. For partial text, use =OR(ISNUMBER(SEARCH($P$1,A2)),ISNUMBER(SEARCH($P$1,B2))).
What happens if both columns match?
OR returns TRUE if either or both columns match, so the row appears once when you filter the helper column for TRUE.
Can the helper column show the department name instead of TRUE?
Yes. Enter =IF(OR(A2=$P$1,B2=$P$1),$P$1,"") in C2 and copy it down. Matching rows show the value from P1; other rows are blank.




