Tip:
Highlight text to annotate it
X
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.