## No more array formulas?

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

**My answer:** No, not to my knowledge

**Kamran Mumtaz: **This is the formula given by Aladin Akyurek without (CSE)...

----------------------------------------------------------------------------------------------------------------------------

This formula is really interesting, I have never seen this approach before. It definitely 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 one of the most popular blog posts here: Extract a unique distinct list

The original **array** formula:

The new ~~array~~ 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 obviously some can.

**Example 2,**

This formula derives from one of the most popular blog posts here: How to return multiple values using vlookup

Original **array** formula:

New ~~array~~ formula in cell E2:

### Download example file

### What would you prefer? Array formulas or somewhat longer and more complicated regular formulas?

* *

### Category: Unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]

Comments(93) Filed in category: Count values, Excel, Unique distinct values

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Comments(78) Filed in category: Excel, Unique distinct values

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]

Comments(53) Filed in category: Excel, Sorted unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

### Category: Vlookup

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

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

Comments(153) Filed in category: Excel, VLOOKUP and return multiple values

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

Comments(74) Filed in category: Excel, Vlookup

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

VLOOKUP and return multiple matches based on many criteria.

Comments(38) Filed in category: Excel, VLOOKUP and return multiple values

### 7 Responses to “No more array formulas?”

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

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 "non-array" 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...