2 Ways to Count Columns in Excel for Vlookup

We need to count the number of columns in Excel to calculate the column index number while using the VLOOKUP function. Counting column numbers is easy for a small data table. But when the column numbers become incredibly large, it becomes too time-consuming to count the columns manually. Thus, I have come up with this article to give you some tips regarding counting columns in Excel for vlookup.

What is Vlookup in Excel?

Vlookup is the short form of Vertical Lookup. It means looking for a particular value across columns.

An example of vertical lookup is, looking for the value 1509014 across the Employee ID column.

What is Vlookup in Excel?

Vertical Lookup with VLOOKUP Function

The VLOOKUP function is the most used function that is used to look up a value vertically. The function has 4 arguments in total. The first 3 arguments are mandatory. The last one is optional.

VLOOKUP Function Syntax

Vertical Lookup with VLOOKUP Function


Argument Explanation

  • lookup_value: The value that you want to look for across columns.
  • table_array: The cell range where you want to look for a specific value.
  • col_index_num: The relative position of a column that you want to extract from the table array.
  • range_lookup: The field that specifies where you want an exact or approximate match between a lookup_value and the table_array.

Method #1: Count Columns in Excel with COLUMN Function for Vlookup

Excel has a built-in function named COLUMN which can count the column index number of a certain cell reference.

COLUMN Function Syntax

The COLUMN function requires a reference as an argument. The reference can be anything such as a cell reference, a cell range, a column range, a  column header, etc.

Based on the input reference, it can calculate the column number of the reference.

Count Columns in Excel with COLUMN Function

When I use A1 as a reference inside the COLUMN function, it returns 1. This means that A1 is located in the first column.

Instead of using a cell reference when I use a cell range, it can also determine which column the reference cell range belongs to. For example, using the range C2:C10 inside the COLUMN function returns 3. This means the range C2:C10 belongs to column number 3; which is the C column.

Case #1: Table Array Starts from Column A

In the attached image, I’ve used the range A1:C10 as the source table. The range E1:G10 is the destination table.

In column G, I wrote a formula using the VLOOKUP function to extract the salaries from column C based on the Employee IDs.

Column C is the 3rd column of the datasheet. To extract this column using the VLOOKUP function we have inserted 3 as the column index number. But instead of inserting 3 as a column count, we can use the COLUMN function with any cell of column C inside the function as a reference. The COLUMN function will automatically return the column count.

Syntax

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Formula

=VLOOKUP(E2,$A$2:$C$10,COLUMN(C2),FALSE)

Count Columns in Excel with COLUMN Function for Vlookup


Formula Argument Explanation

  • E2 is the lookup_value.
  • $A$2:$C$10 is the table_array which starts from column A.
  • COLUMN(C2) returns 3 as cell C2 belongs to column C. From the table_array range $A$2:$C$10 I want to extract the 3rd column. Thus I need to insert 3 as the column index number. But instead of inserting the column index number manually, I used cell C2 as a reference inside the COLUMN function. This is how you can also automate the column counting process.
  • FALSE is meant to ensure an exact match.

Case #2: Table Array Doesn’t Start from Column A

If your table array doesn’t start from column A, you have to subtract the total number of blank columns from the COLUMN function.

In the picture below, you can see the source data table starts from column B. Thus only one blank column is there before the source data table.

Here, the original column count of the Salary column of the source data table is 4. But our table array range is $B$2:$D$10. Inside the table array, the relative column count of the Salary column is 3.

So, to get the actual column count of the Salary column, we have to subtract 1 from the COLUMN function inside the formula.

Formula

=VLOOKUP(F2,$B$2:$D$10,COLUMN(D2)-1,FALSE)

Here, the output of COLUMN(D2) is 4. But the output of COLUMN(D2)-1 is 3. So we’ve got the actual column index number for the VLOOKUP function.

Count Columns in Excel with COLUMN Function for Vlookup

Method #2: Count Columns in Excel with COLUMNS Function for Vlookup

When you want to extract the very last column of a table array, you can use the COLUMNS function to count columns for the VLOOKUP function.

The COLUMNS function can calculate the total number of columns in range. Here, the interesting fact is the total column count is actually the column number of the last column in a table array.

COLUMNS Function Syntax

The COLUMNS function takes a cell range as an input. Then it can return the total number of columns present in the cell range.

That’s why when we use the range A1:A10, it returns 1 because the range only includes a single column.

For the same reason, the COLUMNS function returns 3 for the range A1:C10. Because within the range there are total 3 columns present. They are columns A, B, &C.

Count Columns in Excel with COLUMNS Function

In the following scenario, I’ve extracted salaries from column C into column G. For that purpose, I wrote a formula using the VLOOKUP function.

Syntax

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Formula

=VLOOKUP(E2,$A$2:$C$10,COLUMNS(A2:C10),FALSE)

Count Columns in Excel with COLUMNS Function for Vlookup


Formula Argument Explanation

  • E2 is the lookup_value.
  • $A$2:$C$10 is the table_array.
  • COLUMNS(A2:C10) returns 3. Because there is a total of 3 columns available in the range A2:C10.
  • FALSE is meant to ensure an exact match.

Conclusion

I’ve discussed 2 exclusive ways to count columns in Excel for vlookup. I expect you’ve found this article useful. You can read more articles relating to Excel from the Blog page of our website. Have a fantastic workday!

(Visited 446 times, 9 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *