## Excel array formula: Enter a value in a cell and instantly populate adjacent cells

*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

**Answer:**

**Array formula in cell B2:**

**How to create an array formula**

- Select cell B2
- Type or copy/paste formula above
- Press and hold Ctrl + Shift
- Press enter once
- Release all keys

**How to copy array formula**

- Select cell B2
- Copy cell B2 (Ctrl + c)
- Select cell C2
- Paste (Ctrl + v)
- Select cell range B2:C2
- Copy (Ctrl + c)
- Select cell range B3:C3
- 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**

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 2 Responses to “Excel array formula: Enter a value in a cell and instantly populate adjacent cells”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hey there.. I found this formula very helpful. Question: I just need to populate one column. I mean just the name and not the address. What would the formula look like?

I tried but wasnt successful.

Let me know when u get a chance.

Thanks.

Syed,