Need a Help..Excel Formula lookup

[Sebasti]

Hi,

I have summary of data on Sheet1 and a detailed data on Sheet2 where the same security (as shown in the attachment) has the same or different nominal appearing multiple times. Basically i need a formula on Sheet1 to alert me that Security Nominal in Sheet1 has mismatch with data on Sheet2 column 2. I tried with simple VLOOKUP which helps me a bit, but still requires and thorough check to ensure that im not missing something.

Appreciate if some1 can help.

Thanks

Sebasti

 

[Hui]

Sebasti,

Firstly, Welcome to the Chandoo.org forums

C2: =SUMIFS(Data2!$B$2:$B$69,Data2!$A$2:$A$69,A2)-B2=0
Copy down
Investigate cells with values False

 

[Sebasti]

Dear Hui

Thanks for your reply...However the catch is that Nominal in Data1 may not necessarily be the sum of nominal on Data2 Column B For eg:

Data1(Sheet1) Data2 (Sheet2)
Security Nominal Security Nominal
Macquarie 50,00,0000 Macquarie 50,000,000
Macquarie 28,000,000
Macquarie 50,000,000

Here i have 50,000,000 on Data1 and 50,000,000 appearing twice & 28,000,000 on Data2. If i use a vlookup from Data1 to Data2 based on Security and give an if function, vlookup at times look only on the 1st data on Data2, which is 50,000,000 and gives me "TRUE" result but we have 28,000,000 also in that column.

For me this look a bit cumbersome

Thanks
Sebasti

Sebasti,

Firstly, Welcome to the Chandoo.org forums

C2: =SUMIFS(Data2!$B$2:$B$69,Data2!$A$2:$A$69,A2)-B2=0
Copy down
Investigate cells with values False

 

[Hui]

Using Macquarie as an example
Sheet 1 has $50M
Sheet 2 has $50M, $50M, $50M, $2.8M, $50M
so what do you want to see on Sheet 1 or Sheet 2 ?

 

[Sebasti]

Using Macquarie as an example
Sheet 1 has $50M
Sheet 2 has $50M, $50M, $50M, $2.8M, $50M
so what do you want to see on Sheet 1 or Sheet 2 ?

Yes you are right. I want to see the data on Sheet1 which is 50 Mn is matching with the data on Sheet2. In this macquarie case it should give me a mismatch since Sheet2 has 50Mn and 28Mn

Thanks

 

[Hui]

With the data on Sheet 2
Select it all and got Insert Table, Include headings
The on Sheet1: C2
=COUNTIFS(Table1[Security],A2)-COUNTIFS(Table1[Security],A2,Table1[Nominal],B2)
Copy down
It will show the number of Non matching values for each Security/Nominal combo
See attached

 

[Sebasti]

With the data on Sheet 2
Select it all and got Insert Table, Include headings
The on Sheet1: C2
=COUNTIFS(Table1[Security],A2)-COUNTIFS(Table1[Security],A2,Table1[Nominal],B2)
Copy down
It will show the number of Non matching values for each Security/Nominal combo
See attached

Hey, many thanks this works well if i change my condition.

I can see happy bunnies around