INDEX MATCH

Quick reference

• 00:04 All right, so we've looked at index and match separately.
• 00:08 But when they got together, it was murder.
• 00:09 Sorry, old heart to heart fun from the 70s there.
• 00:14 I'm showing my age.
• 00:16 Index Match, more powerful than VLookup and HLookup.
• 00:20 Let me explain why.
• 00:25 They're often used together to perform lookups.
• 00:27 And is advantageous because they just got more flexibility.
• 00:32 MATCH, for instance, supports wildcard characters for approximate matches.
• 00:36 And INDEX and MATCH can be used to search arrays, or the values
• 00:41 to the left of the first column, or both the lookup value in a table.
• 00:46 This is what you do. In fact, these are on different sheets.
• 00:47 This is the whole thing.
• 00:49 It's just more powerful.
• 00:50 So I really suggest that you use these instead.
• 00:52 Let's have a look at some examples.
• 00:54 All right, let's start putting things together.
• 00:57 We've gone through a few lessons now.
• 01:00 We can start putting things together.
• 01:01 You might see on here we've actually got the balance sheet again,
• 01:05 good old balance sheet needs to balance.
• 01:07 If you don't know anything else, so the two totals in rows 9 and 11,
• 01:11 were just inputs in this particular example, need to be equal.
• 01:16 I put a check in row 13.
• 01:17 So simply when the two don't balance it flags with a red wand Conditional
• 01:22 formatting to the rescue there.
• 01:24 And so, I want to know when is the first month that my balance
• 01:29 sheet doesn't balance.
• 01:31 That's what I'm looking for.
• 01:32 So, I've got lots of ones.
• 01:34 So, I've got duplicates.
• 01:35 It's not in strict ascending order.
• 01:37 It's not in strict descending order.
• 01:39 So, I need to find out what's the way to do this.
• 01:42 Now, I've already shown here that good old H lookup.
• 01:46 This formula doesn't work.
• 01:48 So I can't use this.
• 01:50 So what am I going to use instead there?
• 01:51 And I'm going to use something called a one dimensional index match.
• 01:56 I'll explain why I'm calling it one dimensional in a moment.
• 01:59 But let's look at it in two parts.
• 02:02 First of all, let's find out where the first one occurs.
• 02:06 What position?
• 02:07 First one occurs in position, I will use a match,
• 02:14 just completely because pardon me,
• 02:19 the first number one in this vector become a zero.
• 02:25 It's in position five.
• 02:27 And so the corresponding date, Is
• 02:32 therefore going to be, use the index function.
• 02:35 =index, open brackets, here's my dates, and it's going to be the fifth one.
• 02:42 So the answer is 42886, cuz it's a serial number.
• 02:49 So I need to format it, so I'll use this May 17th, and that's right.
• 02:54 Now, there's something called the principal of staggered development,
• 02:59 which is basically,
• 03:00 you can actually build up a more complicated formula by taking it in turns,
• 03:04 and building it up one bit at a time, and them putting them into each other.
• 03:09 If that sounds like ramble, ramble, ramble that's fine,
• 03:13 it probably is, but let me explain.
• 03:16 This here is an index based on the result of K16, that's K16.
• 03:20 So why don't I take this formula that's in K16,
• 03:25 and just copy it, and then in here,
• 03:29 instead of putting K16, go paste?
• 03:33 This is the legendary index match function.
• 03:35 This is the only, I dont' need this anymore.
• 03:39 Goodbye, you are the weakest link.
• 03:41 And this, I can just steal this, Ctrl+C,
• 03:44 copy and formula bar and put it here in the formula bar.
• 03:48 Bang, May 17th.
• 03:52 I don't need any of this rubbish over here then.
• 03:53 It's very, very flexible.
• 03:56 Notice I've got duplicates, notice I'm looking
• 04:02 up a date which is in a row above where the lookup values are.
• 04:08 Can't do that with h look up which is why that gives you hash value.
• 04:12 Simple as that.
• 04:14 That's a one dimensional example.
• 04:15 Remember this one we had before?
• 04:19 Good old oranges are not the only fruit.
• 04:21 And what I did if you recall is used it to arrange things,
• 04:25 when I went like this and went control shift F3, top row, left column.
• 04:30 This is now Summer, you'll see in the name box.
• 04:34 This is now oranges, so if I want to go how many cells do I make for
• 04:41 oranges in summer, I just type equals oranges,
• 04:47 space in the intercept operator,
• 04:51 SUM And we go press Enter, 180.
• 04:56 That's it, this 180 here being the intersection of these two ranges.
• 05:00 But it's not dynamic, if I wanted to do something else, I can't do that.
• 05:05 So look, I'm gonna use data validation here.
• 05:07 I'm gonna do Summer.
• 05:09 And I'm going to go oranges here, and I'm going to write the formula again.
• 05:15 Now to do this, I'm going to locate my match.
• 05:19 My matches, so
• 05:19 again I'm using this Principles of Staggered Development equals match.
• 05:23 Find me Summer in this range, comma zero.
• 05:30 It's in position two.
• 05:31 Quite right, it's the second one down.
• 05:34 Find me oranges in this
• 05:39 range, comma zero.
• 05:43 It's in position two.
• 05:48 Easy peasy, yep.
• 05:49 Now what I've got to do here is use a big index, =index{,
• 05:53 using the array from this time so it's two dimensional.
• 05:57 I've got rows and columns so I need two matches.
• 05:59 So I'm gonna use this whole thing here and comma.
• 06:01 I want the row number.
• 06:03 Well the row number's given by the seasons so it's going to be this thing here.
• 06:07 That comma column number is going to be G17.
• 06:11 118, same way.
• 06:18 Pinch plus daggard development again,
• 06:23 this is to be taken from here for the copy.
• 06:28 So for E16.
• 06:30 And then let's try G16 I should say in here.
• 06:32 I can actually replace G16, by that, press enter.
• 06:37 Then back here for G17, control c, press enter,
• 06:42 and now put G17 here.
• 06:46 I go paste, and I've got good old index match match.
• 06:51 Not needing any of this stuff.
• 06:53 Look at that.
• 06:53 Index match match.
• 06:54 Two dimensional.
• 06:56 Looks horrible when you see it like this, sorta just about meets the rule of
• 07:00 thumb but it's flexible rather sort of completely transparent.
• 07:04 Remember, sometimes it's a balancing act.
• 07:06 The difference over that, okay, that's simpler,
• 07:09 but I can change this to what did I get in winter for oranges.
• 07:14 And I've got winter and oranges, I've got 300.
• 07:16 That's the correct answer.
• 07:19 How good is that?
• 07:20 That is the most powerful look-up you can go.
• 07:24 This is used all the time in financial modeling to learn index match.
• 07:29 Let's move on.

Lesson notes are only available for subscribers.