Originally published: 27/11/2018 10:39
Publication number: ELQ-58113-1
View all versions & Certificate
Publication number: ELQ-58113-1
View all versions & Certificate
How to Handle large data tables with ease | VLOOKUP COLUMN and ROW
Learn how to handle large data tables with ease!
Introduction
VLOOKUP COLUMN and ROW: Handle large data tables with ease
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.
- Step n°1 |
VLOOKUP COLUMN and ROW
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.
- Step n°2 |
VLOOKUP COLUMN and ROW
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.
