Formula to highlight differences based on two worksheets

Question

Hi,

I have been struggling with this one for a while, and am just doing it manually at present, so any help would be appreciated.

I have a monthly report template. Each month, I download new data and paste it into "New data" sheet in the monthly report template.

There will be new rows, but there will also be updated data in the existing rows in "Existing data".

For example, Col Q = Status
Last month ("Existing data"), the status for Row A was "New", and this month (in "New data") it is "Submitted"
I would like to be able to highlight the differences.
They do have the same Col A = ID

I have tried using a VLOOKUP:
=IF(ISNA(VLOOKUP(A1,Newdata,17,FALSE)),"DIFFERENT","")

But this is returning DIFFERENT for all cells, when I manually cross check it, they aren't different.

Can anyone help?

Thanks in advance!

Answer 1

Hi, @Tanya Sharma

You are welcome.

You can have a try of this function: =IF(VLOOKUP(A1,"Data range",17,FALSE)="First data need to compare", "SAME', "DIFFERENT")

If you have any updates, please feel free to share with us.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in email-notifications to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi, @Tanya Sharma

You can use Conditional Formatting to highlight the differences.

Open the two worksheets and select the data you want to compare in 'New data' sheet. Then click Home tab and choose Conditional formatting->New rule.

Then click 'Use a formula to determine which cells to format'. In 'Format values where this formula is true', type =A1<>'Existing Data'!A1 then choose Format and go to the fill tab to choose the background color you want to use. Then click OK to complete it.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in atp-safelinks.html to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi, @Charles Qi_MSFT ,

Thank you for your answer.

That would be a great option, except Row 2 on Sheet 1 is not necessarily Row 2 on Sheet 2.

That is why I used the VLOOKUP to find the ID on the second sheet.

Thanks,

Answer 4

You can´t try with the XLOOKUP funtion. This function can work with the first three arguments (very similar to VLOOKUP or HLOOKUP) but also contains a fourth argument named "if not found" that is similar a IFERROR function. So to this way only compares if there or not a match and indicates "Different" if no there match for each cell. If ther match you see the same value that exist for two columns (in the different sheets).
IF(VLOOKUP(A1,"Data range",17,FALSE)="First data need to compare", "SAME', "DIFFERENT")
XLOOKUP(A1,"Array (in this case a column) where do you want look up the value to compares","Array (in this case a column) that to want to return from the other worksheet", "DIFFERENT")