# 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.

## 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.

### 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.

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

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

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)

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