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

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**

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

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 […]

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

### 2 Responses to “Excel array formula: Enter a value in a cell and instantly populate adjacent cells”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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,