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 ...
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
