Next Video: The linear regression model
VLOOKUP COLUMN and ROW: Handle large data tables with ease
Feel free to download our Exercise file and practice along with the video. And don't worry, there is also a Solution file with all the formulas.
We have more explainers on advanced Excel functions you might be interested in - INDEX and MATCH; VLOOKUP and MATCH; INDIRECT, VLOOKUP and INDIRECT.
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 a 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 I4, I should obtain 1. Great, so it works ok! If I type B4 to B8 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.
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 B5 again. Sounds strange? It 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.
Ok, here the array includes an additional cell, and the number becomes 3. We’ve 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 that if I equalize this cell to the COLUMNS from B5 to I5, I will see … 1, 2, 3… 8?
Yes, that’s correct. What if I specify a one-column range instead, spread along the vertical axis? I get 1, which is what we expected, right?
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 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.
All right. 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. Good. 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 Column D, which would be cell F4 in our case. Don’t forget that B4 must be anchored.
All right.
Then, we 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 5 horizontally adjacent cells and thus produced 5 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 6. The sixth column in our lookup array refers to Company E.
Therefore, if we pasted the same formula to the row before, we should expect to see 1 and 6, right?
Perfect!
This is how you can use VLOOKUP COLUMN and ROW to handle large data tables with ease.
We hope this explainer was useful to you. Next, you can watch our video explainer about VLOOKUP and MATCH another useful Excel functions combination and take our Introduction to Excel course to enhance your skills.
