Function Problems

Find Rows Where Either of Two Excel Columns Contains a Value

Bushra ParveenBushra Parveen Nov 18, 2026 870 views

Question details

Find every row where either of two columns matches a specified text value.

Find Rows Where Either of Two Excel Columns Contains a 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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the search value

Type Finance in cell P1. You can change this cell later to search for another department.

2
Add the formula

Enter a header such as Match in C1, then enter =OR(A2=$P$1,B2=$P$1) in C2.

3
Copy the formula down

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.

4
Filter matching rows

Select the data range, choose Data > Filter, and use the filter arrow in the Match column to show only TRUE.

Use an OR helper formula to find exact matches
Adjust the references: If your data is in columns K and L, use =OR(K2=$P$1,L2=$P$1) instead. Keep P1 outside the filtered data columns.
Find matching rows in WPS Spreadsheets

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. 1. Open the spreadsheet: Open your workbook in WPS Spreadsheets and locate the two columns to search.
  2. 2. Set the search value: Enter Finance or another department in P1.
  3. 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. 4. Filter the results: Select the data range, choose Data > Filter, and use the Match column's filter arrow to display TRUE rows.
Find matches in either of two columns with an OR formulaChange the search value in one cell without editing each formulaOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and filters for free
microsoft office alternative - wps office

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.