Convert array formula to a regular formula
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones seem fine.
Why would you want to avoid array formulas? An unexperienced Excel user may turn it into a regular formula by editing it and then press Enter. That will break the array formula, and often but not always, an error will be returned.
Now, if you can't avoid an array formula, you could lock the cell containing the array formula and protect the worksheet. I will explain how to do this, as well.
The following conversation inspired me to write this article.
CSE is an abbreviation for CTRL + SHIFT + ENTER, which is how you enter an array formula.
Kamran Mumtaz: This is the formula given by Aladin Akyurek without (CSE)...
This formula is exciting, I have never seen this approach before. It looks like an array formula, but it is not. It opens up possibilities that I have not thought about before.
Example 1,
This formula derives from the following article: Extract a unique distinct list
The original array formula:
The new regular formula in cell B2:
The new formula is NOT an array formula! Amazing! I am not sure if all array formulas can be converted to regular formulas, but some can.
Update: I am now using an even smaller regular formula that is not using this method at all. The LOOKUP function can do things that would generally require an array formula. See this article for more: Extract a unique distinct list
Explaining formula in cell B2
The formula in column B extracts unique distinct values from column A without the need to create an array formula.
I will explain below the calculation steps and the workaround that makes this possible.
Step 1  Count cells based on condition(s)
The COUNTIF function counts cells in a cell range based on a condition. COUNTIF(range, criteria), range is $B$1:B1 and criteria is $A$2:$A$5.
This seems perhaps wrong, but it is not. $ B$1:B1 is a cell reference that expands when the cell is copied and pasted to cells below. The dollar signs in $B$1 make this part of the cell ref absolute, meaning it is locked.
The other part is B1, and that cell ref is relative, meaning it changes from cell to cell, for this to work you need to copy the cell and not the formula.
COUNTIF($B$1:B1,$A$2:$A$5)
becomes
COUNTIF("Unique distinct values", {"AA";"BB";"CC";"AA"})
and returns {0; 0; 0; 0}.
This means that "Unique distinct values" is not equal to any value in this array {"AA";"BB";"CC";"AA"}. This method requires you usually to enter the formula as an array formula. However, the next step is a workaround.
Step 2  Create a regular formula
Generally, If the COUNTIF function returns an array, this would require you to enter the formula as an array formula. However, if you encapsulate the COUNTIF function with the INDEX function you can create a workaround. Unfortunately it comes with a cost, the formula grows larger.
INDEX(COUNTIF($B$1:B1,$A$2:$A$5),0,0)
becomes
INDEX({0; 0; 0; 0},0,0)
and returns {0; 0; 0; 0}.
You can shorten the INDEX function to
INDEX(COUNTIF($B$1:B1,$A$2:$A$5),)
and the result will be the same.
Step 3  Find first value not displayed
The MATCH function returns the relative position of the first value that is equal to 0 (zero). MATCH(lookup_value, lookup_array, [match_type])
MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$5),0,0),0)
becomes
MATCH(0,{0; 0; 0; 0},0)
and returns 1. 0 (zero) is equal to the first value in the array.
Step 4  Return value based on row number
The INDEX function returns a value from a given cell range based on a specified row number. INDEX(array, [row_num], [column_num])
INDEX($A$2:$A$5, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$5), 0, 0), 0))
becomes
INDEX($A$2:$A$5, 1)
and returns "AA" in cell B2.
Example 2,
This example comes from this article: How to return multiple values using vlookup. The formula returns values based ona condition, if value in cell E1 is equal to one or many values in cell range A2:A5 the formula will return the corresponding values from B2:B5.
Original array formula:
The new regular formula in cell E2:
Explaining formula in cell E2
Step 1  Compare values
The equal sign lets you check if a value is equal to another value or multiple values.
$A$2:$A$5=$E$1
becomes
{"AA";"BB";"CC";"AA"}="AA"
and returns {TRUE; FALSE; FALSE; TRUE}.
Step 2  Create an array of numbers from 1 to n
We need an array of numbers starting from 1 to n where n is the total number of rows in the cell range.
MATCH(ROW($B$2:$B$5), ROW($B$2:$B$5))
becomes
MATCH({2;3;4;5}, {2;3;4;5})
and returns {1;2;3;4}. There are four rows in cell range $B$2:$B$5.
Step 3  Multiply logical expression with array
The asterisk character allows you to multiply numbers, arrays, and constants in a formula. Use parentheses to control the order of operation.
($A$2:$A$5=$E$1)*(MATCH(ROW($B$2:$B$5), ROW($B$2:$B$5)))
becomes
{TRUE; FALSE; FALSE; TRUE}*(MATCH(ROW($B$2:$B$5), ROW($B$2:$B$5)))
becomes
{TRUE; FALSE; FALSE; TRUE}*{1;2;3;4}
and returns {1;0;0;4}.
TRUE and FALSE are boolean values, their numerical equivalents are 1 and 0 (zero). For example TRUE*1=1, FALSE*1=0.
Step 4  Check if values are not equal to value in E1
This step contains a logical expression that returns TRUE if a value is not equal to cell E1 and FALSE if equal. Combine a less than and greater than character to check if values are not equal.
$A$2:$A$5<>$E$1
becomes
{"AA";"BB";"CC";"AA"}<>"AA"
and returns
{FALSE; TRUE; TRUE; FALSE}.
Step 5  Multiply with a value greater than the maximum row number
A worksheet can contain up to 1048576 rows, a larger number will return an error in step 9.
($A$2:$A$5<>$E$1)*1048577
becomes
{FALSE; TRUE; TRUE; FALSE}*1048577
and returns
{0; 1048577; 1048577; 0}
Step 6  Add arrays
The plus sign + lets you add arrays.
($A$2:$A$5=$E$1)*(MATCH(ROW($B$2:$B$5), ROW($B$2:$B$5)))+($A$2:$A$5<>$E$1)*1048577
becomes
{1;0;0;4}+($A$2:$A$5<>$E$1)*1048577
becomes
{1;0;0;4}+{0; 1048577; 1048577; 0}
and returns
{1; 1048577; 1048577; 4}
Step 7  Remove need for array formula
The INDEX function lets you do calculations without requiring you to create an array formula.
INDEX(($A$2:$A$5=$E$1)*(MATCH(ROW($B$2:$B$5), ROW($B$2:$B$5)))+($A$2:$A$5<>$E$1)*1048577, 0, 0)
becomes
INDEX({1; 1048577; 1048577; 4}, 0, 0)
and returns
{1; 1048577; 1048577; 4}
Step 8  Extract kth smallest number in array
The SMALL function extracts the kth smallest number in the array, this step makes sure that different values display in every cell.
SMALL(INDEX(($A$2:$A$5=$E$1)*(MATCH(ROW($B$2:$B$5), ROW($B$2:$B$5)))+($A$2:$A$5<>$E$1)*1048577, 0, 0), ROW(A1))
becomes
SMALL({1; 1048577; 1048577; 4}, ROW(A1))
The ROW function contains a relative cell reference that changes when you copy the cell and paste to cells below.
SMALL({1; 1048577; 1048577; 4}, ROW(A1))
becomes
SMALL({1; 1048577; 1048577; 4}, 1)
and returns 1.
Step 9  Return value from cell range
This step utilizes the INDEX function to fetch a value from cell range B2:B5.
INDEX($B$2:$B$5, SMALL(INDEX(($A$2:$A$5=$E$1)*(MATCH(ROW($B$2:$B$5), ROW($B$2:$B$5)))+($A$2:$A$5<>$E$1)*1048577, 0, 0), ROW(A1)))
becomes
INDEX($B$2:$B$5, 1)
and returns 1 in cell E2.
The IF function has a logical expression that compares multiple values to a condition.
How to lock cells
You can lock cells so they can't be edited and that would make it safer to use array formulas. Keep in mind that there are tools out there that can open up passwordprotected sheets easily.
All cells in a worksheet are locked by default, you can verify this. Select any cell in your worksheet, press CTRL + 1 to format cell. Go to tab "Protection".
This tab shows you if the cell is locked, the checkbox is enabled if it is locked. To lock cells you need to protect the worksheet.
 Go to the worksheet you want to protect.
 Go to tab "Review" on the ribbon.

Click on "Protect sheet" button.
 Enter a password you want to use. Remember it because you will need it next time you want to unprotect the worksheet.
 Click OK button.
What do you prefer? Array formulas or somewhat longer and more complicated regular formulas?
Find last matching value in an unsorted list
I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about [โฆ]
Find the longest/shortest consecutive sequence of a value
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell [โฆ]
How to use the AGGREGATE function
The AGGREGATE function allows you to calculate different specific functions to a list or database. A special AGGREGATE function feature [โฆ]
How to ignore error values using the SMALL function
The image above shows you a formula in cell D3 that tries to get the smallest number from cell range [โฆ]
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me [โฆ]
Find positive and negative amounts that net to zero
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, [โฆ]
How to enter array formulas in merged cells
Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: [โฆ]
Sum cells containing numbers and text based on a condition
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and [โฆ]
7 Responses to โConvert array formula to a regular formulaโ
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
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
Interesting Formula, but if the purpose is to avoid the CSE, and its Excel2010, then this is far simpler formula:
=INDEX(Price,AGGREGATE(15,6,(ROW(Product)MIN(ROW(Product))+1)/(Product=$E$1),ROW(A1)))
No CSE required.
chrisham,
Great comment! From now on, I am going to provide both array formulas and regular formulas. I am going to let the reader decide which formula to use.
Iยดll also update the most read posts with their equivalent formulas.
Wow.. nice.. I HATE array formulas..
It doesnt work with multiple columns so I daisy chained them together sorting A1:A6 and putting the result in C1:C6. Then sort B1:B12 and put the result in C7:C19.
Now sort C1:C19 and put the results in D1:D19...
If you dont like the #N/A, =IFERROR() will get change them to something more palatable.
Awesome.. thanks guys!!
One more comment.. this is a very processor intensive formula. I used it for a thousand cells (just pulled it down) and it takes +4 minutes to open the spread sheet and will often crash.
I narrowed down the problem to the formula and just use it on 120 cells the spreadsheet opens quickly and calculates/recalculates quickly.
Thanks again guys!
JL
Jeff,
Yes, array formulas can be cpu intensive.
Oscar,
This "nonarray" formula concept may be of some benefit after all. I had been working on making workbooks to load into Office on Windows Phone 8 that would be tailored for our field personnel. Only to find that Excel in Office for Windows Phone does NOT support array formulas (among other things).
By modifying the examples Chrisham used, it has proven to be a worthwhile endeavor. I'd be interested in other examples should any be out there.
Thanks and regards,
Leroy
I liked this formula so I could get a list of items, but then do a calculation on the resulting list. I also wanted the original list to be of any length, and so I wouldn't know the length of the resultant list. With the #N/A error, this means I would have to go back and manually adjust the length of the final list (because the #N/A would mess up the calculations.
I adapted the above formula to replace any #N/As with blanks:
=IF(ISERROR(INDEX($A$2:$A$11,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$11),0,0),0))),"",INDEX($A$2:$A$11,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$11),0,0),0)))
It's sort of clunky, but it worked for me. The double quotes could be replaced with whatever filler you would need in case your calculations call for it...