Search code examples
excelalgorithmgoogle-sheetsexcel-formula

I want to create a excel formula for comparison of data between 2 sheets and return back in colour codes

I have 2 excel documents. Document1 is a master data and Document2 is the data I extracted from SAP. I want to compare document2 data to document1 data with multiple sheets(Sheet1,Shee2,Sheet3). So result should return in such a way that document2 should reflect in different colour if it matches in sheet1, different colour if it matches to sheet2 data and same for sheet3. Sheet1,sheet2 and sheet3 data are not same.

I tried to put XLOOKUP and use it in conditional format but I couldn't do it. So need your help

Solution

  • It is difficult without example data. But assuming you haven't left out any important details, this should be quite simple.

    I suggest you look at using COUNTIFS in three different conditional formatting rules. One for each sheet.

    Something like:

    =COUNTIFS(<SheetXColumn>, <Doc2Value>)>0