Excel checking information & results in columns

Copper Contributor

Hi there

I am trying to speed up something I have to do manually at work every morning.


Columns A & B are exported from software: A is a product code. B is the price assigned to that product code.

Columns C & D are exported from daily sales books. C is product code. D is the price that was charged for it. 

I want column C to search column A and tell me if the price in Column D is the same as the price in Column B for that product code or not.


Having a complete brain block!!!!


Any help appreciated

2 Replies


Option 1: using an extra column.

In E1, enter the text "Same Price".

In E2, enter the formula

=LET(p, XLOOKUP(E2, A:A, B:B, ""), IF(p="", "No match", p=D2))

Fill down.

Column E will contain TRUE if the prices are the same, FALSE if they are different, and "No Match" if the product cannot be found in column A.


Option 2: using conditional formatting.

Select from D2 down to the last used row in columns C/D. D2 should be the active cell in the selection.

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

=XLOOKUP(C2, A:A, B:B, "")<>D2

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

Thanks for that Hans. I had already got a reply which seems to have disappeared but which worked.
It is interesting to see the different ways of doing it so I will have a play with your suggestions also.
Many thanks