Do you want to know the secret to building complex, advanced Excel formulas? Here it is… 👇
Get inspiration from LEGO.
Confused? Let me demonstrate.
Calculating Student with Highest GPA
Let’s build a formula to calculate the student with best GPA out of a table like this:
Our data is in columns C& D, in the range C6:D45.
- We can easily lookup the name of a student with GPA of say, 3.53, using lookup formulas like XLOOKUP.
- We can also calculate the maximum (best) GPA using the MAX formula.
When you combine the Ideas 1 & 2, just like you combine LEGO, you create a third formula that gets the name of student with highest GPA.
So the final formula would be:
=XLOOKUP(MAX(D6:D45),D6:D45,C6:C45)
And now, you can do the happy dance.
How about second highest GPA Student?
We can extend this idea to get the name of student with second highest GPA. Instead of MAX, we need to use LARGE function. Like this:
=XLOOKUP(LARGE(D6:D45, 2),D6:D45,C6:C45)
In the above formula, LARGE(…, 2) returns the second highest GPA.
You can use the same concept to get third highest or second lowest (use SMALL(…,2)).
The LEGO way of building complex formulas...
Just as there are a few basic building blocks in LEGO (well, nowadays, you can find a weird block shaped like dragon’s eyeball to fit in your harry potter set, but you get my point), we can also construct a complex formula from few basic ingredients.
Keep this construction tips in mind…
- Break down the complex problem to small manageable chunks
- Write down formulas for these chunks
- If you need to repeat some logic, consider using either helper cells, or the new LET() function
- Utilize the power of Excel references (absolute - $A$1, relative A1, mixed - A$1, $A1 and finally structural (table[column]) to write shorter & simpler formulas
- Try newer formulas like FILTER, UNIQUE, SORT, LAMBDA etc. to get results easily
- Take time to learn new techniques - such as spill ranges, LAMBDAs, data type driven formulas
- When you have time, challenge yourself to figure out another formula to solve the problem
5 Examples of how to build complex formulas
If you are curious about this, then definitely watch my video. It shows how to write advanced formulas with Excel. See it below or on my YouTube channel.
Sample Workbook - 5 Complex Formulas
If you would like to practice these formulas, here is the sample workbook.
Share your answers in the comments section.
Want more inspiration? Check out below examples:
There are heaps of advanced formula examples on chandoo.org. Start with below and see how you can twist the same 2×2 block in umpteen ways.
- Formula Forensics – a series of amazing Excel formula trickery
- Multi-condition Lookups – using good old VLOOKUP in creative ways
- 10 Advanced IF formula tricks – the trusty IF can do wonders
- Dynamic Array Formulas – HOT HOT HOT – You must try these.
3 Responses to “How to write complex Excel formulas (hint: it’s a lot like LEGO)”
Can we include an "array" as well?
If we're using Excel 365, then XMATCH (or XLOOKUP) can perform an ascending or descending search without actually having to sort the data. So you don't even need the MAX function to find the highest GPA.
=INDEX(C6:C45,XMATCH(99^9,D6:D45,-1))
And since we're already using Excel 365, SORTBY and SEQUENCE can handle getting the top five.
=INDEX(SORTBY(C6:C45,D6:D45,-1),SEQUENCE(5))
I have a list of work orders with different line numbers that have individual statuses. I am trying to return a list of all work orders (no line numbers) in which ALL line numbers share the same status. I have tried to use filter and no luck. I have tried to use xlookup to simply return the work order if all work orders have same status. I cannot figure this out. Any help?
tried =xlookup(wo#,wo#=@wo#*status="d",wo#,"",0,1) and it is not giving me the result