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

My book: Guerrilla Data Analysis 2nd Edition

[praneshbiswas]

I am looking for this, exactly this for a long time! You have no idea how grateful I am to you for the video. Just continue to be awesome! Liked and subscribed! Godspeed.

[stevennye]

This was a very good explanation of the advantages of Index-Match_Match, it looks so difficult the first time I saw this in one of Leia's courses, it takes a while to get used to using it, but for me understanding Index Match was a breakthrough, it is worth the effort.

[salahhasan]

Simple and smart approach ! Well done 👍

[excelchamps]

INDEX + MATCH + MATCH is a life saver. And, you know the best part you don't need to update the formula if you're using a table. And sometimes, I love to use vlookup and match for two way lookup as well. But, the thing is to get out of this nested if trap.

[optimistprime]

I like the 2 way lookup. Like you said, it's cleaner and much easier to read.

[kwik]

Im stoked to add you to my arsenal of excel gurus Oz. STOKED!!!

[jazzista]

Great solution Oz. Index and match together is so powerful. I use them all the time . I don't use Vlookup at all since vlookup has many limitations (cannot look left for instance) and it is not  as robust as Index and match together. Thanks

[XLarium]

Probably the best choice in this case would be SUMPRODUCT().