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

### Category: Excel

How to return multiple values using vlookup in excel

This post explains how to lookup a value and return multiple values. No array formula required.Comments(441) Filed in category: Excel, VLOOKUP and return multiple values

Lookup and return multiple values concatenated into one cell

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. […]Comments(249) Filed in category: Concatenate, Excel, Textjoin

How to create a dynamic chart (excel 2003 and 2007)

Question: How do I create a chart that dynamically adds the values, as i type them in the workbook? Answer: […]Comments(161) Filed in category: Charts, Excel, Interactive

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

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