Transcripts for the above video clip:

MATCH FUNCTION:

The MATCH function allows you to find the relative reference in any array of a particular cell. In English what this means is, in this row of cells, if we want to find out where the 30th of April is automatically we can use the MATCH function & it will tell us that the 30th of April is in the 4th cell of this array. To use the MATCH function, you click on a cell that you wish to put the MATCH function in, we'll activate the function wizard, & we'll find the Match function, & we say ok. And what you'll see is the first thing it asks is tell me the lookup value. Now in this case, the lookup value is the value you use to find the value you want to array. In this case we want it to look at this cell. The lookup array is the contiguous range of cells containing possible lookup values. In this case we want to say find this cell in this row here. And then the match type is a number of 1 , zero or -1 indicating which value to return. Now it's always difficult to remember which these are, so I find it's quite useful to just click on the help on this function feature. What you'll see, help pops up, & you can look through this to get a bit more. But basically here it will tell you if you type a one, MATCH finds the largest value that is less than or equal to the lookup value. If you type a zero, MATCH finds the first value that is exactly the same. And if you type a minus one MATCH finds the smallest value that is greater than or equal to. In this case, what I want to do is find the exact value, so I'm going to type a zero in here. And when we click ok, you'll see it tells me that this cell can be found in the fourth cell in our row of cells. The real benefit of this function is found when items change in your rows. So for example let's change this cell to 29th Feb 2008. And what you'll see is that all these cells are going to change. So now 30th of April has moved one earlier & you'll see that the MATCH function has identified that & says it's now sitting in column 3. Another thing it does well is if in this example we had these numbers up here, so we try now to manually identify what columns these cells are in. If I come here & insert a column you'll see that that doesn't update so in this manual method we'd still be looking at 3, but the MATCH function has identified because we are looking at that full row of cells, that 30th of April has actually moved to the fourth column. So this function is very useful particularly when you are making use of the VLOOKUP function.