2 Ways to Count Columns for VLOOKUP in Excel

Sometimes we want to count the column number in excel for VLOOKUP.For a simple dataset, counting column numbers is simple.However, as the column numbers reach incredibly big, manually counting the columns becomes excessively time-consuming. Here we will illustrate 2 methods to count columns in excel for VLOOKUP function.

Contents hide

📁 Download Excel File

Learn to Count Columns for VLOOKUP Function in Excel with These 2 Suitable Approaches

1. Using COLUMN with VLOOKUP Function

2. Applying COLUMNS with VLOOKUP Function

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

Counting Column Number for VLOOKUP.xlsx

Learn to Count Columns for VLOOKUP Function in Excel with These 2 Suitable Approaches

VLOOKUP function takes 4 input arguments. The lookup value, the range which contains the lookup value, Column number in that range where we will get the return value, true of false depending on what kind of match we are looking for. Here we will demonstrate 2 ways to count columns in excel for VLOOKUP using the COLUMN and COLUMNS functions.

Approach

1. Using COLUMN with VLOOKUP Function

In the first method, we will use the COLUMN function. This COLUMN function helps us to get the column number so that we don’t have to look for the column number manually. Here we will use the COLUMN function for counting column index numbers for VLOOKUP. Here we have a table that contains the student id, student name, and obtained marks.

⬇️⬇️ STEPS ⬇️⬇️

Here, we have a table named as Table1 in this dataset. From this table we will pick out the desired column and place them on the COLUMN worksheet.

After that, go to the COLUMN worksheet and select cell D6.

Enter following formula.

=VLOOKUP(B6,Table1,COLUMN(Table1[Obtained Marks])-1,FALSE)

Then Press Enter key.

After that, select cell D6 and drag the Fill Handle icon from D6 to D11.

Here we can see that we have extracted the obtained marks column from the Dataset-1 Table1 to the COLUMN worksheet.

🔨 Formula Breakdown

VLOOKUP(B6,Table1,COLUMN(Table1[Obtained Marks])-1,FALSE)

👉 Here B6 represents lookup_value.

👉 Here Table1 is the range.

👉 In the table the Obtained Marks is in the 3rd column. But COLUMN(Table1[Obtained Marks]) this will return a value of 4. But the relative column number of Obtained marks in the table is 3. So we will subtract 1 from the COLUMN(Table1[Obtained Marks]). This will return 3.

👉 Finally, False used for exact match.

Approach

2. Applying COLUMNS with VLOOKUP Function

In the second method we will use the COLUMNS function for VLOOKUP. The COLUMNS function actually counts the total number of columns in a range and returns the value which is actually the column number of the last column. So in this method we will use the COLUMNS function for VLOOKUP.

⬇️⬇️ STEPS ⬇️⬇️

Firstly, go the Dataset-2.

Secondly, select the data and Name them as Table.

Then go to the COLUMNS worksheet and select cell D6.

Enter the following formula.

=VLOOKUP(B6,Table,COLUMNS(Table),FALSE)

Press Enter key.

Now select cell D6 and drag Fill Handle icon from D6 to D11.

Here we can see that we got a similar kind of result using the COLUMNS function and extracted the obtained marks column from the Dataset-2 worksheet.

🔨 Formula Breakdown

VLOOKUP(B6,Table,COLUMNS(Table),FALSE)

👉 Here B6 is the lookup_value.

👉 Table represent the data range.

👉 COLUMNS(Table) returns the total columns in the table which in this case returns 3.

👉 False is used for exact match.

📝 Takeaways from This Article

📌 Here we demonstrated two method Count Columns in Excel for VLOOKUP.

📌 In this article we learned the use of the VLOOKUP function.

📌 We also got to know about the COLUMN and COLUMNS function and their uses.

Conclusion

In this article, we tried to show to every possible way to Count Columns in Excel for VLOOKUP. So you may choose the suitable method for you. If you have any questions, please leave them in the comments. Finally, we propose that you visit Excelden to discover more about Excel.