SOLVED

# Help with VLOOkUP MIN Conditional Formatting

30 rows of data. Column A = Names Column B = Yes, No, or N/A Column C = Number (Accounting format). What I'm trying to Achieve:  If Column B = Yes OR N/A; then in Column C, identify the Lowest Number (excluding zero), and bold,  underline the lowest number AND highlighted the cell with a color (e.g. yellow). I need help building this formula.

4 Replies

# Re: Help with VLOOkUP MIN Conditional Formatting

so you can highlight column C and go into Conditional Formatting and select 'Based on custom formula' and then use a formula like:

``=C1=LET(a, FILTER(C:C,(B:B="Yes")+(B:B="N/A"),0), MINIFS(a,a,"<>0"))``

then set the formatting based on what you want (underline, bold, yellow...)
HOWEVER if this sheet is large or gets large this may result in very poor performance. I would recommend adding a cell or Named item that calculates that min value
i.e. the LET(a, FILTER(C:C,(B:B="Yes")+(B:B="N/A"),0), MINIFS(a,a,"<>0"))
and then just refer to that in the conditional formatting.  For example if you put the above in cell Z1 then the formula in conditional formatting would be:

``=C1=\$Z\$1``
best response confirmed by Darlene1270 (Copper Contributor)
Solution

# Re: Help with VLOOkUP MIN Conditional Formatting

``=SMALL(IF(((\$B\$2:\$B\$22="yes")+(\$B\$2:\$B\$22="N/A"))*(\$C\$2:\$C\$22<>0),\$C\$2:\$C\$22),1)=C2``

With e.g. Excel 2013 you can try this rule for conditional formatting. # Re: Help with VLOOkUP MIN Conditional Formatting

Thank you! @mtarler

# Re: Help with VLOOkUP MIN Conditional Formatting

Thank you! this worked perfectly!