vlook up code

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
I have two sheets named Sheet1 and Sheet2, each with two columns, first column is policy number which is unique, second column is payments.
All the Sheet1’s policy number can be found in Sheet2. In other words, the policy record in Sheet2 is always higher than Sheet1’s. And also the policy numbers in the two sheets are not necessary in the same rankings.
I want to create a new sheet named IndiClaim based on the following conditions:
For example, for policy number 10050, Sheet2 shows payment as 230, Sheet1 shows 190, the difference is 40. Then the policy number 10050, Sheet2 column 2 value and the difference 40 will copy value only to sheet IndiClaim’s column 1, 2 and 3;
For a new policy only shown on Sheet2, but if column 2 shows zero value, do not copy. However, if column 2 shows non zero value, copy the policy number and value to sheet IndiClaim’s column 1 and 2. At the same time, copy the second column value to column 3.
All the lookups, copy and paste range begin from row 8 to unidentified last rows.
I appreciate any help.
Dennis
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
is there any reason why you can't just use Sheet2 rather than creating a new sheet ?

on sheet2, cell C8 (copy down)

=IF($B8=0,0,IF(ISERROR(MATCH($A8,Sheet1!$A:$A,0)),$B8,$B8-VLOOKUP($A8,Sheet1!$A:$B,2,0)))

The only difference between this and the new sheet is that those listed with 0 values on sheet2 in B will still be listed, however, you could just use AutoFilter on Sheet 2 column B to hide values with value = 0.
 

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
that's great. thanks for help.
For some other considerations, could you help me out with a code solution?
have a good day.
dennis
 

Watch MrExcel Video

Forum statistics

Threads
1,122,187
Messages
5,594,748
Members
413,930
Latest member
Nela817

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top