Forum Discussion
Excel checking information & results in columns
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
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
