Price Comparison Sheet, highlighting cheapest

Question

I have created a Google Sheet for purchasing some new products and I'd like to highlight which cell on each row is the cheapest. I know how to do this with several Conditional Formatting rules (one for each row), but is it possible to have one rule for the entire range?

Column G has the formula =MIN(B2:E2) for each row so that I can see each cheapest price and total them up at the bottom of the column. The way I would normally do this is select each cell in G and put a Conditional Formatting for range B2:E2 with custom formula =G2 (formatting accordingly).

Can this be done with a single rule applied to the whole range? I've tried what I have found by Googling, but it either highlights all cells in the column or range and not the single cell that matches G for each row.

@badermart player0 seems to have answered the question successfully. So I am holding back on updating (I was in the middle of it!) — neildeadman

player0 player0 · Accepted Answer · 2019-01-27T16:52:44

custom conditional formatting formula you are looking for:

=ARRAYFORMULA(IF(LEN($G2:$G), {{IF(B2=$G2, 1)},
                               {IF(C2=$G2, 1)},
                               {IF(D2=$G2, 1)},
                               {IF(E2=$G2, 1)}}))

ARRAYFORMULA is for overall continuity so the formula would not stop after evaluating 1st cell
IF is obvious so we could nest some rules
LEN checks range G2:G and handles the end of a calculation with the last non-empty cell
{} these are called array brackets and ensure that what's inside them is taken as one piece
},{ comma between array brackets is able to stack these array pieces next to each other (imagine something like a virtual table constructed with 4 virtual columns - in this case)
IF another one so we could evaluate something against something
B2:$G2 one of the 4 ranges (4 because you have 4 shops)
$ this is a lock for G column and makes sure that it looks always on the right column, otherwise, it would continue looking into H column from C column inputs, then I column for D column inputs etc.)
1 is equal to "true" eg. if given IF statement is calculable it returns kinda virtual agreement that given piece is calculated so formula could calculate for another IF chunk
B2:E is the range where conditional format rule should apply. also because this range there is no need to add ranges like (like in usual cases when doing non-conditional formatting calculation) B2:B, C2:C etc. and instead you can use just B2 and because this is conditional formatting formula everything else is handled simply by having conditional format formula - in other words: this whole formula works only as custom formula for conditional formatting (may also work in data validation) but it won't do the job if its pasted in some cell

Price Comparison Sheet, highlighting cheapest

2 Answers