# How to flag the highest value in a list in Excel

Compare each value with the maximum of the list: =IF(B2=MAX($B$2:$B$7),"Top store","").

**Excel:** `=IF(B2=MAX($B$2:$B$7),"Top store","")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Store | Q3 sales | Top? |
| Downtown | 182400 |  |
| Airport | 96750 |  |
| Mall | 210300 |  |
| Harbor | 154900 |  |
| Uptown | 143200 |  |
| Lakeside | 188050 |  |

Formula in C2, filled to C7: `=IF(B2=MAX($B$2:$B$7),"Top store","")`

Result: ; ; Top store; ; ; 

## How it works

MAX finds the highest sales in the locked range, and each row checks whether its own value equals it. Only the Mall matches. If you change any figure, the flag moves on its own.

## Other ways

- `=IF(RANK.EQ(B2,$B$2:$B$7)=1,"Top store","")`: RANK.EQ gives each store its position. Change =1 to <=3 to flag the top three.
- `=IF(B2>=LARGE($B$2:$B$7,3),"Top 3","")`: Flags every store at or above the third-highest value. (Flags Mall, Lakeside and Downtown.)

## Common mistakes

- Lock the range with $, or each row compares against a different slice of the list.
- Ties are all flagged, since each of them equals the maximum.

## Questions

**How do I flag the lowest value?**

Use MIN: `=IF(B2=MIN($B$2:$B$7),"Lowest","")`.

**How do I return the name of the top store?**

`=XLOOKUP(MAX(B2:B7),B2:B7,A2:A7)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/flag-the-highest-value