Why do you use array formulas?
Why can’t we use a normal Excel function?
What does an array formula do?
People often ask me questions like these. A quick answer is that array formulas can be used to answer very complex questions about data.
Sometimes, one array formula can replace thousands of intermediary formulas. Normal functions (i.e. vlookup, sumif, counta) have very specific and limited uses but array formulas are much more flexible and powerful (when used properly).
The best way to learn is to see a lot of examples and then practice.
Array formula Examples
Book:
Read Mike Girvin’s book ‘Control Shift Enter‘ if you want a comprehensive knowledge of array formulas. It is a great book! You will have to invest several hours to read it carefully.
Webpages:
If you would prefer free on-line resources then consider these webpages:
- Chip Pearson,
- Microsoft,
- Charley Kyd,
- Bob Umlas,
- Decision Models,
- Ozgrid,
- Chandoo,
- Databison,
- Tom Urtis.
Videos:
I’ve created several YouTube videos that use array formulas. Here are a few (I’ll be adding more later)
- ‘Why would I need an array formula?‘ A good video to watch if you are just starting out with array formulas.
- ‘Average the largest 3rd, 4th, and 5th values‘ in one formula average the 3rd, 4th, and 5th largest numbers.
- ‘Dynamic Array Hyperlink‘ Use an array to create a seemingly impossible dynamic hyperlink (one of my favorites!).
- ‘Solve An Algebra Equation‘ Not something that you’ll use every day but the concept is interesting.
- ‘Count Unique Values Per Row‘ Counting unique values for many horizontal ranges.
- ‘Key Differences Between FIND and MATCH‘ Learn the difference between the FIND and MATCH functions when used in arrays.
- ‘Find the 2nd Upper Case Letter in a Name‘ Sometimes strange Excel requests can only be easily answered using an array (or vba).
- ‘Multiple Lookups Using Excel’s OFFSET Function‘ Use an array to add the results of several lookups at the same time.