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, the whole row or a single column.

For example, if I type rose and choose an array from before to I four, I should obtain one.

Great.

So it works.

Okay, if I type B four to be eight instead, Excel will count the number of rows we have in that range, and we'll obtain five.

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.

Rose can be used as a counter.

The starting point must be cell B five, anchored to its road number and the array must continue to be five again.

Sounds strange.

Hit 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 guests?

I want to see two written here.

Well, from be five to be six, 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 is Rose, but it counts cells along the horizontal axis.

This means if I equalize this cell to the columns from be five 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?

Hey, it's not a secret that columns could be used as a counter, too.

The logic is identical.

Only this time I will fix the column reference.

When I drag this formula to the right, I obtained 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.

All right, so far, so good.

Let's use columns within a V, look up function and see how this can help us.

The information in this cell will be our look up value, and the entire table will be our array.

Good.

Here is what we will do differently in this example.

The column number will be determined by the columns function.

Who's array initiates from the top left corner of the look up array, namely cell before, and it continues to the right until it stops at Company D, which would be cell F four.

In our case, don't forget that before must be anchored.

All right, then we will need to indicate that we're 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 look up array refers to company.

Therefore, if we pasted the same formula to the row below, we should expect to see one and six.

Right?

Perfect.

This is how you can use columns and rows as a counter in look up functions.

Thank you for watching.