Google Sheets conditional formatting with MAX/MIN if duplicate value in another column

Question

I manage an order table with several products in one column and prices in another (range H8:O, I8:J is merged).

Each product can be sold by different suppliers, so the price of a product can vary.

I would like to do a conditional formatting in the price column to know all the time the most expensive and the least expensive price of each product (most expensive in red and least expensive in green).

I tried several formulas but it doesn't work just one seems close to the desired result, here it is:

=N8=ARRAYFORMULA(MAX(SI(NB.SI($H$8:$H;$H8:$H)>1=VRAI;$N$8:$N)))

=N8=ARRAYFORMULA(MAX(IF(COUNTIF($H$8:$H,$H8:$H)>1=TRUE,$N$8:$N)))

Here is a screenshot of the result obtained with this formula:

I tried a formula that identifies product by product, I find the maximum amount in the column but it colors all the same amounts in the column.

=$N8=MAXIFS($N$8:$N;$H$8:$H;"POELE")

Here is what it gives:

Here is a screenshot of the result I would like:

Thanks in advance for your help.

Answer 1

Use a formula to check that there are no matching products with a higher price (red):

=COUNTIFS(H$8:H$16,H8,N$8:N$16,">"&N8)=0

and to check that there are no matching products with a lower price (green):

=COUNTIFS(H$8:H$16,H8,N$8:N$16,"<"&N8)=0

Edit 1

If you wanted to highlight only the last occurrence of a highest/lowest price you could add an extra condition like this e.g. for the lowest price:

(COUNTIFS(H$8:H$16,H8,N$8:N$16,"<"&N8)=0)*(COUNTIFS(H8:H$16,H8,N8:N$16,N8)=1)

(note that the anchors to the start of the range are removed on the second Countifs)

Edit 2

In Google Sheets as mentioned you can extend the ranges to the end of the column so the three formulas above would be

=COUNTIFS(H$8:H,H8,N$8:N,">"&N8)=0

=COUNTIFS(H$8:H,H8,N$8:N,"<"&N8)=0

and

=(COUNTIFS(H$8:H,H8,N$8:N,"<"&N8)=0)*(COUNTIFS(H8:H,H8,N8:N,N8)=1)

assuming there is nothing below the formatted range.