Function Problems

Excel Formula to Find a Value at Least Twice the Second-Highest Value

Phi Hung VoPhi Hung Vo Nov 18, 2026 868 views

Question details

Find the header of the largest value in a row if that value is at least twice the second-largest value.

Find a Value at Least Twice the Second-Highest Value in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Compare values across a row and return the corresponding column header when the largest value meets the specified threshold.
Observed behavior
The desired result is the largest value's header when the condition is met, or a fallback message otherwise.
Before you start

Place the headers in B1:G1 and the corresponding values in B2:G2, with at least two numeric values in the row. If your data is elsewhere, adjust both ranges while keeping them aligned.

Solution 1Recommended

Use LARGE, INDEX, and MATCH to Return the Dominant Value's Header

Use this formula to compare the largest and second-largest values and return a header or fallback message.

1
Select a result cell

Click an empty cell outside B2:G2, such as H2.

2
Enter the formula

Type =IF(LARGE(B2:G2,1)>=2*LARGE(B2:G2,2),INDEX($B$1:$G$1,MATCH(MAX(B2:G2),B2:G2,0)),"no dominant order") and press Enter.

3
Apply it to other rows

If needed, drag the fill handle from H2 down to other rows. The value range changes by row, while the header range remains fixed.

Use LARGE, INDEX, and MATCH to Return the Dominant Value's Header
Ties and numeric values: If the maximum occurs more than once and the condition is met, MATCH returns the first matching header. LARGE needs at least two numeric values in the row.
WPS Spreadsheet

Find the Dominant Value with WPS Spreadsheet

Use the same LARGE, INDEX, and MATCH formula in WPS Spreadsheet to compare row values and return the matching header.

  1. 1. Open your spreadsheet: Open WPS Spreadsheet and select Open to load your workbook.
  2. 2. Check the ranges: Make sure B1:G1 contains the headers and B2:G2 contains at least two numeric values, or adjust the ranges in the formula.
  3. 3. Enter the formula: Click an empty cell such as H2, enter =IF(LARGE(B2:G2,1)>=2*LARGE(B2:G2,2),INDEX($B$1:$G$1,MATCH(MAX(B2:G2),B2:G2,0)),"no dominant order"), and press Enter.
  4. 4. Copy the formula: Drag the fill handle down from the result cell to evaluate additional rows.
Compare the two largest values and return a column header.Open and save Microsoft Excel XLSX files.Fill the formula down to evaluate multiple rows.
microsoft office alternative - wps office

Frequently Asked Questions

What happens if the largest value is exactly twice the second-largest?

The formula returns the largest value's header because the >= operator includes equality.

What if two columns share the largest value?

If the comparison condition is met, MATCH returns the first matching header from left to right. Otherwise, the formula returns "no dominant order".

Why does the formula return a #NUM! error?

LARGE(B2:G2,2) needs at least two numeric values. Check the cells in B2:G2 and convert numbers stored as text if necessary.

Can I change the fallback message?

Yes. Replace "no dominant order" in the formula with another message in double quotation marks, or use "" to return an empty string.

Can I use the formula with negative values?

Yes, but it applies the comparison literally: the largest number must be greater than or equal to twice the second-largest. Decide whether that condition matches your definition of dominance for negative values.