Hi.

I have numbers in three columns - H, P, X

I need to compare numbers in each row and find out the lowest value and find out if there is a unique lowest value.

For example, in row 2

H2=2, P2=5.4, X2=2. In this row, both H2 and X2 have the lowest number, 2. Hence, the lowest number is not unique. I want to fill cells H2 and X2 in Yellow and cell P2 in Red.

In row 3, 

H3=2, P3=5.4, X3=4. In this row, H3 has the lowest unique number, 2. I want to fill cell H3 in Green and cells P3 and X3 in Red.

Green = lowest and unique

Yellow = lowest but not unique

Red = Not the lowest

I can use the MIN function to find the least number but I do not know how to identify the unique lowest number if available.

Thanks for your help.

• Moved by Lanlan HuangMicrosoft contingent staff 5 hours 48 minutes ago

Select for example H2:H100. I will assume that the active cell within the selection is in row 2.

Color the cells yellow (this will be the default)

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=OR($H2>$P2,$H2>$X2)

Click Format...

Activate the Fill tab and select red.

Click OK, then OK again.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=AND($H2<$P2,$H2<$X2)

Click Format...

Activate the Fill tab and select green.

Click OK, then OK again.

The formulas for columns P and X are similar, switching the roles of the three cells.