Article updated on October 19, 2011

I have been working on a worksheet and cannot figure out the formula that would work to copy to the other cells and result in the duplicate entries being shown.

I want to enter the phone number in the phone column and have the last name and address populate.

Can you give me some guidance? Thanks.
A B C E F G H
1 2 3 4
Phone Last Address 330-555-9235 Adams Mary 12 Oak St.
330-555-8099 Adams Michael 44 Oak St.
330-555-8119 Adams Sam 44 Oak St.
330-555-5566 Jones Mary 36 Main St.
330-555-5327 Palmer Michael 67 ShortSt.
330-555-2227 Powers Sara 27 ShortSt.
330-555-7845 Parker Clarence12 Oak St.
330-555-6565 Parker Mary 36 Main St.
330-555-7901 Smith Clarenc 513 Main St
330-555-7901 Miller Sam 517 E. Main

Array formula in cell B2:

=INDEX(\$B\$6:\$C\$15, MIN(IF(\$A2=\$A\$6:\$A\$15, MATCH(ROW(\$A\$6:\$A\$15), ROW(\$A\$6:\$A\$15)), "")), COLUMN(A1))

How to create an array formula

1. Select cell B2
2. Type or copy/paste formula above
3. Press and hold Ctrl + Shift
4. Press enter once
5. Release all keys

How to copy array formula

1. Select cell B2
2. Copy cell B2 (Ctrl + c)
3. Select cell C2
4. Paste (Ctrl + v)
5. Select cell range B2:C2
6. Copy (Ctrl + c)
7. Select cell range B3:C3
8. Paste (Ctrl + v)

Explaining array formula in cell B2

=INDEX(\$B\$6:\$C\$15, MIN(IF(\$A2=\$A\$6:\$A\$15, MATCH(ROW(\$A\$6:\$A\$15), ROW(\$A\$6:\$A\$15)), "")), COLUMN(A1))

Step 1 - Find cells equal to search value (\$A2)

\$A2=\$A\$6:\$A\$15

becomes

"330-555-8099" = {"330-555-9235";"330-555-8099";"330-555-8119";"330-555-5566";"330-555-5327";"330-555-2227";"330-555-7845";"330-555-6565";"330-555-7901";"330-555-7901"}

becomes

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Step 2 - Convert matching values to a relative row number

IF(\$A2=\$A\$6:\$A\$15, MATCH(ROW(\$A\$6:\$A\$15), ROW(\$A\$6:\$A\$15)), "")

becomes

IF({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, MATCH(ROW(\$A\$6:\$A\$15), ROW(\$A\$6:\$A\$15)), "")

becomes

IF({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, {1;2;3;4;5;6;7;8;9;10}, "")

and returns {"";2;"";"";"";"";"";"";"";""}

Step 3 - Return the smallest number

MIN(IF(\$A2=\$A\$6:\$A\$15, MATCH(ROW(\$A\$6:\$A\$15), ROW(\$A\$6:\$A\$15)), ""))

becomes

MIN({"";2;"";"";"";"";"";"";"";""}) and returns 2

Step 4 - Return a value of  a cell at the intersection of a particular row and column, in a given range

=INDEX(\$B\$6:\$C\$15, MIN(IF(\$A2=\$A\$6:\$A\$15, MATCH(ROW(\$A\$6:\$A\$15), ROW(\$A\$6:\$A\$15)), "")), COLUMN(A1))

becomes

=INDEX(\$B\$6:\$C\$15, 2, COLUMN(A1))

becomes

=INDEX(\$B\$6:\$C\$15, 2, 1)

becomes

=INDEX({"Adams Mary", "12 Oak St.";"Adams Michael", "44 Oak St.";"Adams Sam", "44 Oak St.";"Jones Mary", "36 Main St.";"Palmer Michael", "67 ShortSt. ";"Powers Sara", "27 ShortSt. ";"Parker Clarence12", "Oak St. ";"Parker Mary", "36 Main St.";"Smith Clarenc", "513 Main St";"Miller Sam", "517 E. Main"}, 2, 1)

and returns value "Adams Michael".