Complex IF Formula Converted to 2-Way Lookup with INDEX-MATCH-MATCH

0:00 – what we’re up to
1:21 – looking at the problem
5:12 – a word “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.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2