Question Details

No question body available.

Tags

excel excel-formula conditional-formatting

Answers (2)

Accepted Answer Available
Accepted Answer
July 23, 2025 Score: 2 Rep: 35,388 Quality: High Completeness: 50%

You could try this, which says 'if there is a price with the minimum date for this product which is different from the current price, mark the current price as an error'

=AND(COUNTIFS(A:A,A2,E:E,MINIFS(E:E,A:A,A2),D:D,""&D2),E2"")

There is a slight caveat, in that if you had a situation where there were two different prices for the minimum date in the same product, then all subsequent prices for this product would be highlighted.

A filter would be an alternative:

=LET(filRetail,FILTER(D:D,A:A=A2),filDate,FILTER(E:E,A:A=A2),AND(E2"",D2XLOOKUP(MIN(filDate),filDate,filRetail)))

But I would deprecate this one - works but too slow:

=AND(E2"",D2XLOOKUP(A2&"|"&MINIFS(E:E,A:A,A2),A:A&"|"&E:E,D:D))
July 23, 2025 Score: 2 Rep: 7,673 Quality: Low Completeness: 50%

As of your formula, this one checks Product ID and Retail based on the earliest Date

=IF((XLOOKUP(MIN($E$2:$E$10),$E$2:$E$10,$D$2:$D$10)$D2)*($A2=XLOOKUP(MIN($E$2:$E$10),$E$2:$E$10,$A$2:$A$10)),TRUE,FALSE)

The Apply to is D2:D10.

The first XLOOKUP checks the price(), and the second the ID (=).
Multiplication is an AND logical operation on the two results.

EDIT: (on comment)

=LET(red,FILTER(D:D,A:A=A2),
tim,FILTER(E:E,A:A=A2),
IF(XLOOKUP(MIN(tim),tim,red)D2,TRUE,FALSE))

This formula will check in groups of ID-s for the same criterias. With whole columns, on the sheet there cannot be any irrelevant data in them for proper results.