I Use INDEX MATCH Every Day (Most Excel Users Never Learn This)

0:00 Ask any pro what their go-to lookup formula is

0:02 and they'll all tell you the same thing, index match.

0:05 It's more powerful than XLOOKUP, more flexible than VLOOKUP,

0:09 and once it clicks, you'll never go back.

0:11 In this video, I'm going to show you everything you

0:13 need to know to master index match as fast as possible,

0:17 and by the end of this video, you'll be a pro.

0:19 Let's get started.

0:25 [music] For example, here we have a summary table showing

0:27 total sales for each of our sales rep by quarter,

0:30 and we want to look up the total sales for the rep entered in cell I2,

0:34 so we can quickly pull any reps numbers.

0:36 Instead of scanning through the table manually each time,

0:39 we can use index match to set this up.

0:41 Index match is actually two functions working together.

0:44 Index returns a value from a specific position in an array,

0:48 and match finds the position of a value within a range.

0:51 When you combine them together,

0:53 you get a lookup function that can find any value anywhere in your data.

0:57 Match locates the position of the value,

0:59 and index returns the value at that position.

1:02 To use index match, first enter the index function and select the array

1:06 that contains the value you want to return as the array argument.

1:09 In this case, we want to return total sales,

1:12 so I'm going to select the total sales column.

1:14 Now, we just need to tell index which row we want to return.

1:17 Instead of hardcoding row number,

1:19 we can use the match function to find it for us.

1:22 Just enter the match function,

1:23 select the cell containing the rep name as the lookup

1:25 value because this is the value we're searching

1:28 for, and then enter the sales rep column as the lookup

1:30 array since this is the column we're searching in.

1:33 Lastly, enter zero as the match type to search for an exact match,

1:37 close both functions with parentheses,

1:39 and hit enter to instantly return Taylor Booth's total sales.

1:43 What makes index match so powerful is its ability

1:45 to look at values across both rows and columns simultaneously.

1:50 For example, here we have the same data table and index

1:52 match formula looking at total sales for the rep in cell I2,

1:56 but now we want to add a quarter toggle that looks

1:58 at the sales rep total sales for the quarter selected in I3.

2:02 To do this, all we need to do is replace

2:04 the array argument with all of the quarterly sales values,

2:07 and then add a second match function to look

2:10 up the column position of the quarter we want.

2:12 Just enter match, select the quarter cell as the lookup value,

2:16 select the quarter headers as the lookup array, and set the match type to zero.

2:20 Hit enter, and we've officially created a two-way index match formula

2:24 that automatically updates based on both the rep and the quarter selected.

2:28 You just learned what most Excel users spend years trying to figure out.

2:32 If you found this video helpful,

2:34 hit the thumbs up button and subscribe to our channel for more Excel tips.