How to Create Array Formulas in Excel

0:32 Create a Multi-Cell Array Formula (output more than one value)
1:29 Single-Cell Array Formula (output to a single cell)
1:48 Nested IF Array Formula example

Don't Forget to Subscribe! 😃

FOLLOW VERTEX42:

[johnq]

This was very helpful to me. Thank you

[spockrates]

Great video! But I have a question. How would I go about creating an array formula with an undefined second cell reference? You can do this to some extent in Excel via something like A:A, but in Google Sheets there's an additional level of control, as you can specify something like A4:A. Is there similar functionality for Excel? Thanks in advance!

EDIT: Vertex helped me figure it out, if anyone else is wondering. The trick is to use a count of the whole range as the second reference, as Excel allows you to use formulas as part of an array reference.

Example: =ARRAYFORMULA(A4:A) in Google Sheets becomes A4:INDEX(A:A, COUNTA(A:A), 1) in Excel.

[deez]

Is there a way to make this formula availablr? Im running version 16.45 and the arrayformula function as well as the filter function is not available. When i search it in the formulas list ithey dont even show up so when i type it in cells, i get errors. This is very frustrating because NO ONE has been able to find a solytion. Ive looked all over the internet trying to find a solution to this problem and ive found one person (from 10 years ago) who had the same issue but they weren't very clear as to what is going on. I know i could copy the formula cell by cell but that isnt practical since the information i need to fill the cells with is dynamic. PLEASE HELP ILL PAY LMAO

[sumanthakur]

The formula in Cell C78, kindly review.

[raghasudhamadhan]

I tried the same formula but it is not working

[TODOMATIO]

I’m trying to creat a basic daily balance. All I want is the formula that will take yesterday’s balance, minus today’s expenses, plus today’s income (this will be today’s balance)

[Twinkier]

Why use an array formula when you can put the formula for the product in the cell and drag it down to the rest of the cells? It appears that you arrive at the exact same result. What's the advatage then?