# 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 ID**s.

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.