Complex Excel IF Formula Simplified to a 2-Way Lookup with INDEX-MATCH-MATCH
0:00 - what we're up to
1:21 - looking at the problem
5:12 - a word about "going rogue" as an effective style
6:09 - starting the solution
6:29 - using RANDBETWEEN to make up some data
7:14 - making dropdown lists
8:07 - INDEX-MATCH-MATCH making the 2-way lookup
10:00 - summary and final comments
Someone sent me a spreadsheet that had a large complex formula in it. There were 5 IF statements inside each other and some CHOOSE functions.
*NOTE: this video moves fast because the concepts are more important than the how-to.*
This video focuses on the thought process of:
understanding the formula and user's needs
realizing that the formula is using 2 variables to retrieve a piece of data
the objective is really a 2-way lookup
INDEX-MATCH-MATCH is what we'll use to get Excel to retrieve the data.
Another important point is that I don't try to recreate the person's data inside the original spreadsheet. I focus on the concept and getting the 2-way lookup working.
This way, it's not necessary to try to understand the person's business, terminology, and all the details of how they developed their spreadsheet. Full attention can be devoted to getting the 2-way lookup working. Then, hand it back to the user and let him recreate the mechanism as he needs it.
One thing to appreciate is that the user wrote an effective formula with the IF and CHOOSE functions. This video just shows a cleaner alternative.
#IF
#2WayLookup
#INDEXMATCHMATCH
Website: http://ozdusoleil.com
My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analy...
My old blog: http://datascopic.net/blog-2-2