INDEX

Quick reference

• 00:04 Okay, so I've just about got every single viewer here offside of me now,
• 00:08 because just about everybody on the planet uses V-lookup and
• 00:12 H-lookup functions and I've just told you not to use them.
• 00:16 I've said there's something bigger and better than those two functions.
• 00:19 So I supposed I'd better make a very good case for the defense.
• 00:23 There's two key functions that you need to combine here, and
• 00:25 the first of these important functions is Index.
• 00:28 The index function returns the value of an element in a table or in an array,
• 00:34 selected by the row and column of that indices, that's numbers to you and me.
• 00:40 The Actual syntax is INDEX(array, row_num, column_num),
• 00:45 where array is a range of cells that you actually want to set.
• 00:50 And if it's only got one row or one column, then you only need to use one of
• 00:55 the actual arguments thereafter, I'll show you in a moment with an Excel example.
• 01:00 Essentially what it's saying is look,
• 01:02 if you've just got a row of data then you just need to select a column number.
• 01:07 If you've got a column of data you need to select a row number.
• 01:10 And quite frankly you don't even need to do what's on this slide.
• 01:14 I've copied this from the actual Excel help, but
• 01:18 it's even simpler than this really.
• 01:20 Let me demonstrate.
• 01:23 Let's get rid of the jargon first and foremost.
• 01:25 In this example here, I have got a table in cells B4 to G9.
• 01:31 Like this, known as a table.
• 01:33 It's also know as an array.
• 01:36 Let me explain something here.
• 01:38 This is a cell, that's a cell.
• 01:40 This is a collection of cells.
• 01:41 That's another cell.
• 01:42 And some people think this padded cell is where I belong.
• 01:47 Stop with your joking.
• 01:49 A collection of cells like this is known as a row vector.
• 01:54 This is another row vector.
• 01:58 That's a row vector.
• 01:59 That's a row vector.
• 02:00 This is a column vector.
• 02:01 That's a column vector.
• 02:02 That's a column vector.
• 02:06 So a vector is something that has only one row or only one column.
• 02:11 An array is something that has at least two rows, and at least two columns.
• 02:16 So this is a table right.
• 02:17 And the reason I'm making a big deal out of this is that the index function
• 02:23 has actual different syntax depending on what you're doing.
• 02:27 Now index is pretty much the posh way of saying, pick something from a list.
• 02:34 Or pick something out of a table.
• 02:37 So, if I were saying,
• 02:39 right, I've got the list A, B, C, D, E, what's the fourth one on the list?
• 02:45 A, B, C, D.
• 02:46 D is the fourth one on the list and that's what the index function does.
• 02:50 It says take the list, give me the fourth value, thank you.
• 02:54 Let me show you.
• 02:55 So I'm going to actually, I have here let's go for position four, and
• 03:00 we'll go okay equals index open brackets.
• 03:03 Now according to the help, what I'm supposed to do is if I'm going to
• 03:08 actually pick a column, then remember it's row number, column number.
• 03:13 You go okay, B4 to B9.
• 03:15 I want the fourth one in there, so it's the row number next, here.
• 03:21 So I don't actually need to put in the column number.
• 03:27 And it's worked, it's picked up the 19.
• 03:29 If instead I've done the actual row vector,
• 03:34 I could go =index, open brackets, highlight this range like this.
• 03:40 Now it's supposed to go not the row number, I put the column number in.
• 03:47 And that should give me the number 4, and it does.
• 03:51 Well, you don't need to do that.
• 03:52 You actually can just take comma, that, and Excel knows what you mean anyway.
• 04:00 So you might as well do that, keep it simple, and then you don't need to worry
• 04:03 about row numbers and column numbers which I've seen in some models that we audit.
• 04:08 So that's the vector form of Index.
• 04:12 The array example is when you actually have to pick more than one row and
• 04:17 more than one column.
• 04:18 So let's say I want the second row, third column.
• 04:22 It's simple equals index, open brackets,
• 04:27 highlight the whole table, comma the row number is 2.
• 04:31 The call number is three.
• 04:34 So it's the second row, third column.
• 04:37 Second row is 7,8, 9, 10, 11, 12.
• 04:39 Third column is going to be the 9.
• 04:44 Simple.
• 04:45 That's it.

Lesson notes are only available for subscribers.