Stephanie asks:

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

Answer:

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".

Download excel *.xlsx file

Stephanie