the previous lectures were more
difficult here we will go through a
couple of functions that are
straightforward to use namely rows and
columns they are not a nightmare but
sometimes you might run into one if you
didn't know they existed so let's get
started
here we have the same table that we've
already seen several times rows counts
the number of rows in any given array
it's that simple and it is not a problem
if the array is made up of a single cell
a whole row or a single column for
example if I type rows and choose an
array from b4 to I 4 I should obtain one
great so it works okay if I type b4 to
b8 instead Excel will count the number
of rows we have in that range and we'll
obtain 5 the reasoning behind this
function is when you have to create a
new table that is relatively large you
will need the row number to change
according to a rule let me illustrate
what I mean with a very simple example
rows can be used as a counter the
starting point must be cell b5 anchored
to its row number and the array must
continue to be 5 again sounds strange
hid actually isn't it is just a neat
logical trick now I can copy this
formula and paste it to the three cells
below let's go back and see what
happened how did Excel guess I want to
see two written here well from b5 to b6
we have two rows okay here the array
includes an additional cell and the
number becomes three we fixed the
starting point but the range continues
to expand downwards of course we can do
the same for columns to make a long
story short this function works
precisely the same way as rows but it
counts cells along the horizontal axis
this means if I equalize this cell to
the columns from b5 to I five I will see
one two three eight yes that's correct
what if I specify a one column range
instead spread along the vertical axis I
get one which is what we expected right
it's not a secret that columns could be
used as a counter to the logic is
identical only this time I will fix the
column reference
you
when I drag this formula to the right I
obtain consecutive numbers as you saw
rows and columns function in the same
way they simply refer either to the
vertical or the horizontal orientation
of the cells on a worksheet alright so
far so good let's use columns within a
vlookup function and see how this can
help us the information in this cell
will be our lookup value and the entire
table will be our array here is what we
will do differently in this example the
column number will be determined by the
columns function whose array initiates
from the top-left corner of the lookup
array namely cell b4 and it continues to
the right until it stops at Company D
which would be cell f4 in our case don't
forget that b4 must be anchored all
right then
we will need to indicate that we are
looking for an exact match once we've
done that we can press ENTER let's see
if the function worked correctly yes it
did how so because the columns function
comprised of five horizontally adjacent
cells and thus produced five as an
output what if I paste this formula to
the right will it work let's see what
happens the columns function produced
the number six
the sixth column in our lookup array
refers to Company E
therefore if we pasted the same formula
to the row below we should expect to see
1 & 6 right perfect this is how you can
use columns and rows as a counter in
lookup functions thank you for watching