## How to enter array formulas in merged cells

*Article last updated on July 11, 2018*

Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: *Array formulas are not valid in merged cells.*

This is not true, they are allowed in merged cells. Here are a few suggestions to solve this problem.

### Unmerge cells

- Select the merged cell
- Click "Merge & Center" button to unmerge cells
- Select first cell
- Enter array formula
- Select cells
- Click "Merge & Center" button to merge cells

Tip! You don´t need to repeat these actions for all cells below, just click and hold on black dot on lower right corner of cell and drag down as far as needed.

### Excel defined named ranges

- Go to tab "Formula" on the ribbon
- Click "Name Manager"
- Type a name, I named it
*arrayformula* - Enter the array formula in "Refers to:" field

- Click OK button
- Select the merged cell
- Type
*=arrayformula*in formula bar - Press enter

### Recommended posts

- Absolute and relative references
- Insert an array formula using vba
- Extract a unique distinct list using array formulas

If you want to learn more about array formulas join Advanced excel course.

This post will teach you how to work with excel arrays in visual basic for applications (VBA). Why do you […]

Array formulas allows you to do advanced calculations not possible with regular formulas.

The picture above shows how to merge two columns into one list using a formula. If you are looking […]

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

Combine data from multiple sheets

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & […]

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials […]

Convert array formula to a regular formula

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; […]

### 2 Responses to “How to enter array formulas in merged 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

Hi Oscar,

Greetings. I was trying using the ARRAY Formula in the merged Cell, but facing an issue.

For a simple formula in the merged cell, the formula as explained on the website works perfectly but when I try to use it in another merged cell which contains HYPERLINK, I get #REF. I have pasted the code here for your reference. if you could please let me know what is wrong in this code.

=HYPERLINK(INDEX('01-08-2011'!$C$4:$L$129,MATCH(SEARCH!$C$2,'01-08-2011'!$C$4:$C$1159,0),11))

Thanks and regards,

Ali.

Done... Its Resolved :D

Thanks and regards,

Ali.