Compare Row Cells in Excel and Return a Matching Value or Error
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.

- 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.
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.
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.
Open the worksheet and select E3, next to the values in B3:D3.
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.
Drag the fill handle from E3 down column E to check the corresponding cells in each row.

Compare A and S entries in older versions of Excel
Use nested IF and COUNTIF functions when the only expected nonblank entries are A and S.
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. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Select the result cell: Click E3 beside the row containing the values in B3:D3.
- 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. Fill the formula down: Drag the fill handle from E3 down column E to compare the other rows.

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.




