I have two separate excel worksheets - both have a column which contains a reference number and there are common and unique numbers in both sheets. I need to combine the two sheets and align the two columns so that the rows of data from the second sheet line up with the rows of data from the first using the common policy number. The data in the third column needs to align with the data in the 2nd column so effectively there would be some spaces in the final result. Policy Number Policy Number Ref

86119946117 86219941732 a

86119700141 66509837115 b

86120463761 86120700505 c

86120166364 86219442365 d

86120705856 65508447920 e

86119459680 86119946117 f

8611025800 86119700141 g

65509155938 86120463761 h

86220712280 86120166364 i

86120206618 86120705856 j

A =VLOOKUP() should get you what you want. With your data like:

Sheet 1

A B C 1) Policy Number 2) 86119946117 3) 86119700141 4) 86120463761 5) 86120166364 6) 86120705856 7) 86119459680 8) 8611025800 9) 65509155938 10) 86220712280 11) 86120206618 Sheet 2 A B C 1) Policy Number Ref 2) 86219941732 a 3) 66509837115 b 4) 86120700505 c 5) 86219442365 d 6) 65508447920 e 7) 86119946117 f 8) 86119700141 g 9) 86120463761 h 10) 86120166364 i 11) 86120705856 j

On Sheet 1, Cell B2 enter the formula:=VLOOKUP(A2,Sheet2!$A$2:$B$11,1,0)

On Sheet 1, Cell C2 enter the formula:

=VLOOKUP(A2,Sheet2!$A$2:$B$11,2,0)

Drag down 10 rows and your Sheet 1 should now look like:

Sheet 1 A B C 1) Policy Number 2) 86119946117 86119946117 f 3) 86119700141 86119700141 g 4) 86120463761 86120463761 h 5) 86120166364 86120166364 i 6) 86120705856 86120705856 j 7) 86119459680 #N/A #N/A 8) 8611025800 #N/A #N/A 9) 65509155938 #N/A #N/A 10) 86220712280 #N/A #N/A 11) 86120206618 #N/A #N/A

The #N/A is the error value that means "no value is available"

in other words the =VLOOKUP() found no match.If you do not want the #N/A to display then use the formula:

On Sheet 1, Cell B2

=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$11,1,0)),"",VLOOKUP(A2,Sheet2!$A$2:$B$11,1,0))

On Sheet 1, Cell C2

=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$11,2,0)),"",VLOOKUP(A2,Sheet2!$A$2:$B$11,2,0))See how that works for you.

MIKE

Ask Your Question

Weekly Poll

Do you think Amazon should have bought Whole Foods?

Discuss in The Lounge

Poll History