# 3 Ways to Find Column Index Number in Excel Vlookup

Often we have to work with datasheets with numerous columns. I am frequently asked the question of how to find the column index number in a worksheet while using vertical lookup? Now, what is **C****olumn Index Number** in Excel in case of vlookup or vertical lookup? In a simplified way, it is the position number of a column in a referred data table. Now, I’m going to show you **3 easy **means to find a **column index number** in Excel vlookup.

## What is the Column Index Number in Excel VLookup?

In both the **VLOOKUP** function and the **INDEX-MATCH** combination, the concept of a “column index number” remains the same. The column index number represents the column from which you want to retrieve data in a vertical lookup. Whether you use **VLOOKUP** or **INDEX-MATCH,** this number is essential to specify the location of the data you’re interested in.

Here’s how it works in both scenarios:

### A. Using VLOOKUP Function

In the **VLOOKUP** function, the column index number is explicitly provided as the third argument **(col_index_num).**

This number indicates which column in the **table_array** (the range where you’re searching for data) contains the data you want to retrieve.

For example, if you want to retrieve data from the third column of the **table_array,** you would set the **col_index_num** to **3.**

**=VLOOKUP(lookup_value, table_array, 3, FALSE)**

### B. Using INDEX-MATCH Combination

In the **INDEX-MATCH** combination, you don’t specify the column index number directly as you do in the **VLOOKUP** function.

Instead, you use the **MATCH** function to find the position of the column you want to retrieve data from based on a header or criteria.

The **MATCH** function returns the relative position (column number) of the header or criteria within a row or range.

This position is then used as the column index number in the **INDEX** function to retrieve the desired data.

=INDEX(table_array, MATCH(lookup_value, lookup_column, 0), 3)

In the **INDEX-MATCH** example above, **MATCH(lookup_value, lookup_column, 0)** returns the relative position of the **lookup_value** within the **lookup_column.**

This position (which is the same as the column index number in the **table_array**) is used in the **INDEX** function to specify which column in the **table_array** to retrieve data from.

So, whether you use **VLOOKUP** or **INDEX-MATCH,** the column index number represents the location of the data you want to extract in a vertical lookup operation within Excel.

**Introduction to the Dataset**

Before going to the solutions, first, let me introduce you to our dataset for this article. We have some details of a real estate company here with its Invertor names, their property names, and a few other pieces of information.

**Easiest Way to Find Column Index Number in Excel Vlookup**

Using the **Match function** is a handy approach for finding a column index number in Excel.

**Syntax**

*=MATCH(lookup_value, lookup_array, [match_type])*

**Usage Guide**

**Step_1: **Insert two new columns in an empty place beside the data table.

I added two columns- **Column Name** (**column F**) and **Column Index** (**column G**).

**Step_2: **Input the column header names from the main table into the **Column Name** (**column F**).

**Step_3: **Select the entire dataset (click on a cell and press **CTRL+A**).

**Step_4: **Click on the **Insert **tab from the ribbon.

**Step_5: **Under the **Tables **group, hit the **Table **command.

Then the **Create Table **dialog box will pop up.

**Step_6: **Make sure the **My table has headers **checkbox is checked.

**Step_7: **Hit the **OK **button.

This will give the table a **Table Name**. The **Table Name **will appear on the top-left corner of the ribbon.

The table of my worksheet is named **Table3**.

**Step_8: **Type this formula inside the cell **G2**:

**=MATCH(F2,Table3[#Headers],0) **

**Formula Explanation**

**F2**is my specified cell address.**Table3**mentions the table name.**#Headers**selected the column headers of**Table3**.**0**is the Match Type for the exact match.

**Step_9: **Press **ENTER **to apply the formula.

** **

**Step_10: **Take your mouse cursor to the lower bottom corner of cell **G2**, and the cursor will turn into **Fill Handle**.

**Step_11: **Double-click on the **Fill Handle**.

**Final Result**

All the column index numbers will come along according to their column names immediately.

**🔗 2 Ways to Count Columns in Excel for Vlookup**

**🔗 [Excel VBA] Count Columns & Rows Having Data (10 Editable Codes)**

**Alternative Method #1: Use the COLUMN Function to Find Column Index Number in Excel Vlookup**

In this method, I am going to show how can use find column index numbers by applying the **COLUMN function**.

**Syntax**

*=COLUMN([reference])*

Not to mention that I am going to use the previous datasheet here. The first few steps are like the previous method.

**Usage Guide**

**Step_1: **Insert a new table in the same sheet with two columns.

The column headers are **Column Name (column A)** and **Column Index (column B)**.

**Step_2: **Insert the column header names from the main table into **Column Name (column A)**.

**Step_3: **Select the entire dataset (**CTRL+A**).

**Step_4: **Hit the **Insert **tab.

**Step_5: **Click on the **Table **command from the **Tables **group.

The **Create Table **dialog box will appear on the screen.

**Step_6: **Make sure the **My table has headers **checkbox is checked.

**Step_7: **Hit the **OK **button.

The **Table Name **will appear on the top-left corner of the ribbon.

**Table1 **is the name of my selected data table.

**Step_8: **Now copy this formula inside the cell **B12**:

**=COLUMN(Table1[[#Headers],[Worth]])**

**Formula Explanation**

**Table1**is the mention of the selected date range.**#Headers**select the column headers of**Table1**.**Worth**is a sample column header name. The above formula returns the column index number of the column**“Worth”.**

**Step_9: **Repeat writing the formula in the rest of the columns according to the header names.

Formulas for the columns **Property Name**, **Investor** **Name**, & **Property** **ID** respectively.

`=COLUMN(Table1[[#Headers],[`**Property Name**]])

`=COLUMN(Table1[[#Headers],[`**Investor** **Name**]])

`=COLUMN(Table1[[#Headers],[`**Property ID**]])

**Final Result**

This way you can find out any column index number easily. All you have to do is input the header names manually.

**🔗 [2 Cases] Count Columns in Excel Until a Certain Value Reached**

**🔗 3 Methods to Convert Column Number to Letter in Excel**

**Alternative Method #2: Use the VLOOKUP Function to Find Column Index Number in Excel**

Before getting into the method direction, I would like to inform you that, this method only works for the data tables that start in **column A**. The **VLOOKUP function** is a handy way to find the **column index numbers**. All you have to do is recall the syntax of its formula.

**Syntax**

*=VLOOKUP(lookup_array, table_array, column_index_number, [range_lookup])*

Suppose, I am trying to figure out the column index number of header **Worth **(**column D**).

**Usage Guide**

**Step_1: **Select the whole data table.

**Step_2: **Click on the **Insert **tab.

**Step_3: **Under the **Tables **group, hit the **Table **option.

The **Create Table **dialog box will pop up on the sheet.

**Step_4: **Click on the **OK **button.

It gives the table, a **Table Name**.

**Step_5: **Put your mouse cursor in an empty cell.

I have selected cell **F2 **from a spare column named **Position **(**column F**).

**Step_6: **Insert the formula in cell **F2**:

**=VLOOKUP(A2,Table2,COLUMN(Table2[Worth]),0)**

**Formula Explanation**

**A2**is the first cell address of the data table.**Table2**is the name of the table.**Worth**is my targeted column.**0**is for the exact match.

**Step_7: **Press **ENTER **to apply the formula.

**Step_8: Double-click** on the cell value.

The applied **VLOOKUP** formula appears instantly with the syntax below it.

**Step_9: **Take your mouse cursor upon** col_index_num** in the syntax and click on it.

This will select the portion of the **column index number** from the applied formula.

**Step_10: **Press **F9**.

**Final Result**

This way the** col_index_num **will show the index number of our selected column.

**Note. **If the **F9** key from your keyboard does not give the result of the **column index number**, press **Fn+F9** together.

**🔗 Count Rows in Excel Until a Certain Value Reached [2 Cases]**

**🔗 6 Ways to Count One Column in Excel If Another Column Meets Criteria**

**Conclusion**

I think now you have a clear idea about what a **column index number** is and how you can find the **column index number** from an Excel sheet. Feel free to leave a comment if you have any confusion about this article. Let us know how you like your website. Thank you.