Sylvia has attended:
Excel Advanced course
Excel Intermediate course
Comparing data in 2 tables
Hi
I am trying to compare data in 2 tables.
Table one has headings
Deal #, Deal Net Revenue $
Table 2 has headings
Deal #, Deal Appportioned Net Revenue
I need to see if:
Deal #s in both tables will correspond, but there may be some missing.
I need to know for those ones that are in both tables, if the deal Net Revenue from the first table equals the deal apportioned net revenue in the 2nd table.
What is the most effective way of doing this? thought of a if with a vlookup nested in it, but there must be a simpler way?
RE: Comparing data in 2 tables
Hi Sylvia
I think you're on the right track with IF and Vlookup.
Here's a simple example bases on your question
Sheet1
Deal # Deal Net Revenue
1 1000
2 7000
4 2000
5 2000
6 3000
7 5000
8 7000
9 2000
11 2000
12 3000
13 5000
15 900
16 100
17 150
Sheet2
Deal # Deal Apportioned Net Revenue
1 1000
4 2000
5 2000
6 3000
7 5000
8 7000
9 2000
12 2000
13 5000
15 900
16 60
18 150
Use the following formula in cell C2 of Sheet2 then copy down.
=(VLOOKUP(A2,Sheet1!A:B,2,FALSE))-B2
It returns
0 where Deal Net Revenue = Deal Apportioned Net Revenue.
A value showing Deal Net Rev - Apportioned net rev
#N/A is where Deal # in Sheet2 does not appear in Sheet1
Result looks like this
Deal # Deal Apportioned Net Revenue
1 1000 0
4 2000 0
5 2000 0
6 3000 0
7 5000 0
8 7000 0
9 2000 0
12 2000 1000
13 5000 0
15 900 0
16 60 40
18 150 #N/A
If you want to replace the #N/A with text such as "Not Present" you can use IFERROR. Formula becomes
=IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE)-B2,"Not Present")
Hope that helps
Doug
Best STL
