How to Count Columns in Excel for Vlookup [2 Ways]

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. However, 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.

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

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.

Why Count Columns in Excel for Vlookup?

We need to count columns in Excel for vlookup because, when we use functions like VLOOKUP or vertical lookup operations with INDEX-MATCH, we need to specify the column or range from which we want to retrieve data. Counting columns is essential because it helps us to identify the exact location of the data we need within a table or range of data.

In VLOOKUP, the col_index_num argument requires you to specify the relative position of the column in the table_array where the desired data resides. Counting columns helps you determine this position accurately.

Similarly, in INDEX-MATCH, you use the MATCH function to find the column index number based on criteria, which may involve counting columns.

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.

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)

Formula 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 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 the 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 of 3 columns present. They are columns A, B, &C.

In the following scenario, I 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)

Formula 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!

Frequently Asked Questions

How do you count a VLOOKUP in Excel?

To count occurrences of a value returned by VLOOKUP in Excel, use the COUNTIF function. Here’s a concise formula: =COUNTIF(range, VLOOKUP(lookup_value, table_array, col_index_num, FALSE))
Replace:

• range: The range where you want to count occurrences.
• lookup_value: The value you’re looking up.
• table_array: The range of data containing the lookup table.
• col_index_num: The column index number to retrieve the value.

For example: =COUNTIF(A:A, VLOOKUP(B2, ‘Sheet1’!A:C, 3, FALSE))
This formula counts how many times the value returned by VLOOKUP in column C of ‘Sheet1’ appears in column A. It provides a dynamic way to count occurrences based on VLOOKUP results.

What is the column limit for VLOOKUP?

In Excel, the VLOOKUP function has a column limit of 1,024 columns. This means you can search for a value in the leftmost column of a range that contains up to 1,024 columns. If your data spans beyond this limit, consider alternative approaches such as restructuring your data or using a different method like INDEX and MATCH for more flexibility.

How do you get the column number for VLOOKUP?

To find the column number for VLOOKUP without manual counting, use the MATCH function in combination with VLOOKUP. The MATCH function locates the position of a specified value within a range.

Syntax: =VLOOKUP(lookup_value, table_array, MATCH(lookup_value, lookup_range, 0), FALSE)

Example: =VLOOKUP(A2, ‘Sheet1’!A:B, MATCH(“HeaderName”, ‘Sheet1’!1:1, 0), FALSE)

In this example, MATCH(“HeaderName”, ‘Sheet1’!1:1, 0) finds the position of “HeaderName” in the first row of ‘Sheet1’ and returns the corresponding column number. This dynamic approach avoids the need for manual counting, making your VLOOKUP formula more flexible and easier to maintain.

How do you do a VLOOKUP without counting columns?

To perform a VLOOKUP without manually counting columns, use column references instead. Instead of specifying a column number, use the column’s letter or name in the table array.

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

Example: =VLOOKUP(A2, ‘Sheet1’!A:B, 2, FALSE)

In this example, ‘Sheet1’!A:B represents the entire table. Instead of specifying a column number, use the column letter (‘B’) to indicate the second column.

By using column references, you make your formula more robust and easier to understand, especially when dealing with large datasets where manual counting can be error-prone.