Change Array Formula to Non-Array and Add a "like" instead of "="

[DinoMania]

Hello.

I have an array formula that gives me the intended results, BUT the data is too large to use an array. Could anyone help me change the formula to a non-array function?

=INDEX('Order History'!E:E,MATCH(1, IF('Order History'!A:A=C3, IF('Order History'!C:C= "INITIAL ORDER SETUP",1)),0))

Also, instead of having 'Order History'!C:C= "INITIAL ORDER SETUP", I would like to just have it search for anything starting with "Initial".

Let me know if you need any other specifics. In advance, thank you for your patience and help.

 

[SpillerBD]

To get away from the array sheet-formula you would have to deal with that in the VBA space. It would still be an array evaluation because that is inherent in the task you're working with.
"BUT the data is too large to use an array." Because your calculation takes long or fails? More memory... Use Access, (DAX and PowerQuery?) Switch to Manual calculations...
Or a good excuse for a new computer. Check out Lenovo for some good deals I7 machines.

Pardon the changes in cell references, but for the begins with part:

{=INDEX('Order History'!E:E,MATCH(1, IF('Order History'!A:A=C3, [B]IF(LEFT('Order History'!C:C,7)= "INITIAL"[/B],1)),0))}

 

[Domenic]

Try converting your data into a Table...

Ribbon > Insert > Tables > Table

Then reference the actual ranges in your formula, instead of whole columns. The ranges will automatically adjust as data is added/removed.

 

[DinoMania]

Thank you both.

SpillerBD the formula works great. I just hate how long the array formulas take to process.

I love power query so far, and I need to learn how to make custom columns that reference a different table.

 

[Aladin Akyurek]

Another option...

In F2 of Order History enter and copy down:

=A2&"|"&C2

Now you can invoke...

=INDEX(E:E,MATCH(C3&"|"&"INITIAL"&"*",F:F,0))

 

[SpillerBD]

I just hate how long the array formulas take to process.

Mine with 30k+ rows only takes 5 minutes to recalculate... Coffee break and some chat.
I am able to turn off calculations and/or change the formulations to values.