Excel copy two cells of data from one sheet to another sheet if a third cell data variable matches the first sheet variable

Question

I work in an insurance agency. We have Sheet1 with a column "Policy Number" (Sheet1,Column B) with all different policy numbers but need the associated column "Policy Effective Date" (Sheet1,Column I) and the column "Policy Expiration Date" (Sheet1,Column J) dates copied from Sheet2 "only" if the Policy Number (Sheet2,Column B) equals Policy Number (Sheet1,Column B) then copy/paste the associated Policy Effective Date from Sheet2,Column I to Sheet1,Column I and the associated Policy Expiration Date copy/pasted from Sheet2,Column J to Sheet1,Column J.

Example:

Sheet1

Column B Column I Column J
Policy Number Policy Effective Date Policy Expiration Date
123456789

Sheet2
Column B Column I Column J
Policy Number Policy Effective Date Policy Expiration Date
123456789 12/1/2012 12/1/2013

Answer 1

This should work for you:

=VLOOKUP($B5,Sheet2!$B:$J,COLUMN()-1,FALSE)

Edit

For the time being “quick and dirty” (more complex formulae would do more work for you but I’m not sure it is worth the effort – unless you say so).

1. In Sheet1 insert a ‘new’ ColumnB, put =A3*1 in B3 and copy down.
2. In Sheet2 copy ColumnC and insert as a ‘new’ ColumnA.
3. In Sheet1!C3 put =VLOOKUP($B3,Sheet2!$A:$C,2,FALSE) and copy to D3.
4. In D3 change ,2, to ,3,.
5. Select C3:D3 and double click on the bottom RH corner.
6. In C28 and D28 change $B to $A.
7. Select C28:D28 and double-click on the bottom RH corner.

Assuming there are no formulae elsewhere, probably best then to select each sheet and Paste Special Values over the top and then get rid of any surplus.

Note that all of this is based on the requirement being 'once off' rather than ongoing/routine.