Function Problems

Compare Row Cells in Excel and Return a Matching Value or Error

Elise WilliamsElise Williams Nov 18, 2026 870 views

Question details

Return the shared nonblank value from columns B through D, ERROR when values conflict, or n/a when all three cells are blank.

Compare Row Cells in Excel and Return a Matching Value or Error
Product
Microsoft Excel
Device & OS
not provided
Scenario
A formula in column E checks the entries in columns B through D on the same row.
Observed behavior
Each row should show its common nonblank value, ERROR for differing values, or n/a if no values are present.
Before you start

Check which row contains the first set of values and adjust the B3:D3 references if needed. The formulas below ignore empty cells and formulas that return an empty string; comparisons are not case-sensitive.

Solution 1Recommended

Compare the row with UNIQUE and FILTER in Microsoft 365 or Excel 2021

Use a dynamic-array formula to check any nonblank values across the row.

1
Select the result cell

Open the worksheet and select E3, next to the values in B3:D3.

2
Enter the formula

In the formula bar, enter =LET(values,UNIQUE(FILTER(B3:D3,B3:D3<>"","n/a"),TRUE),IF(COLUMNS(values)=1,INDEX(values,1,1),"ERROR")) and press Enter.

3
Apply it to other rows

Drag the fill handle from E3 down column E to check the corresponding cells in each row.

Compare the row with UNIQUE and FILTER in Microsoft 365 or Excel 2021
How the comparison works: FILTER removes blank entries, and UNIQUE compares values across columns. One distinct value is returned; multiple distinct values produce ERROR.
WPS Spreadsheet

Compare Row Values in WPS Spreadsheet

Use a SUMPRODUCT and LOOKUP formula in WPS Spreadsheet to return the shared row value, flag a conflict, or show n/a for an empty row.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Select the result cell: Click E3 beside the row containing the values in B3:D3.
  3. 3. Enter the comparison formula: Enter =IF(SUMPRODUCT(--(B3:D3<>""))=0,"n/a",IF(SUMPRODUCT(--(B3:D3<>""),--(B3:D3<>LOOKUP(2,1/(B3:D3<>""),B3:D3)))>0,"ERROR",LOOKUP(2,1/(B3:D3<>""),B3:D3))) and press Enter.
  4. 4. Fill the formula down: Drag the fill handle from E3 down column E to compare the other rows.
Compare nonblank values across a rowReturn ERROR when entries conflictOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and fill controls
microsoft office alternative - wps office

Frequently Asked Questions

What happens if only one cell in B3:D3 has a value?

The formula in E3 returns that value because the other cells are ignored.

Why does the UNIQUE formula use TRUE?

The TRUE argument tells UNIQUE to compare columns, which is necessary because B3:D3 is a horizontal range.

Can I compare a different set of columns?

Yes. Replace every B3:D3 reference in the formula with your desired single-row range, such as C3:F3, and enter the formula outside that range.

Does the formula distinguish uppercase and lowercase text?

No. The comparisons shown treat text such as A and a as matching.

What if a source cell contains an Excel error?

The formulas are intended for values and blank cells. Correct source errors such as #N/A in B3:D3 before comparing the row.