in this extract we will see two
interesting excel functions index and
match will show them within the same
video because they are often applied
together their combination offers an
interesting alternative to vlookup let's
see the application of these two
functions separately and then combined
index is a formula which returns the
value located at a given intersection
within an array so basically the index
formula needs us to indicate the
following one an array where it will
function to a row number three a column
number the formula will go and find the
specified row and the specified column
within the array and deliver its content
for example if we select the array from
before to see 12 in our table and choose
5 as the row argument and 2 as the
column argument we will obtain Italy as
a result the index function simply
delivers the cell which has the
coordinates we chose within the fifth
row and second column of the range we
selected lies Italy right we indicated
those coordinates and index provided us
the result let's do another try I'll
select the same range from b4 to c12
this time let's pick one as the row
argument and again one as the column
argument the result is Borussia given
that the value in the first row and the
first column of the selected range is
Borussia the next function we will see
in this video is match match returns the
relative position of an item within an
array let's illustrate that with an
example I'll type the match function the
first argument that needs to be selected
is the lookup value in our example that
will be Milan lying in B 19 after that
we need to specify which is the array
where the lookup values position needs
to be found let's select all teams that
lie within the range from b4 to b12 the
third argument is a logical value 0 or 1
standing for an exact match and closest
match I'll select 0 as we need an exact
match our formula is ready the output of
the function is 2 which represents
Milan's position within the selected
array it is important to notice that the
formula can be applied also vertically
for example if we go and look at number
games played within the array from b3 to
f3
the result will be four which is correct
as games played is the fourth column
within the array let's consider the
combination of index and match together
this is a pretty powerful tool often
considered superior to vlookup as it
allows users to have a flexible lookup
value within the source table it is
intuitive to combine index and match the
first formula needs as an input relative
positions within a range and the second
formula provides that let's see how the
two functions are applied together in
practice we have a task similar to the
one before two tables the one on the
left is our source table and the one on
the right needs to be filled let's do
the exercise in two steps first will
apply the match formula to each of the
blank cells in the table on the right so
we have + match
the lookup value is Milan in cell i-4
let's fix its column reference as this
will be the lookup value of our table
okay
let's select as a lookup array the cells
from b4 to b12 and fix them we are
looking for an exact match and I'll type
0 as the third argument of the formula
let's copy the formula on the row below
as well the results that we obtained are
that Milan is in the second position and
hamburger is in the fourth position
within the selected array and this is
correct now let's copy the two formulas
for the rest of the blank cells ok so
what we have here is the position of the
lookup value within the source table it
does not do much work but only for now
let's type index in front of the match
function and add a left parenthesis
index needs an array as a first argument
the array is the range of cells among
which the output of the index formula is
selected let's select the cells from c4
to c12 and fix their row references
then we can close the parentheses
the result we obtained is Italy let's
recap how the two functions work
together the index formula needs as an
input the number of the row within a
range right and we have found the number
we are looking for thanks to the match
function in other words the match
function indicates the position of the
result and the index function extracts
it remember the index functions first
argument is the array of the source from
which we want to extract the result the
second argument of the index function is
the match function which finds the exact
piece of data that we are looking for
now I'd like to show you an example of
when index match is superior to vlookup
let's cut the values under the column
team within the source table and paste
them in column G nothing changed within
the table on the right this is because
the combination of index and match works
even when the lookup value is on the
right how do we used vlookup and done
this we would have seen an instant n/a
error message the combination of index
and match is great for situations when
you have an original source sheet and
you don't want to change anything in it
and your lookup value is on the right we
hope that this video was useful to you
thanks for watching