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.


📁 Download Excel File


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.

Using COLUMN function to Count Columns in Excel for Vlookup

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

  • Enter following formula.
=VLOOKUP(B6,Table1,COLUMN(Table1[Obtained Marks])-1,FALSE)

Utilizing COLUMN function to Count Columns in Excel for Vlookup

  • Then Press Enter key.

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

Applying COLUMN function to Count Columns in Excel for Vlookup

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

Using COLUMNS function to Count Columns in Excel for Vlookup

  • Then go to the COLUMNS worksheet and select cell D6.

  • Enter the following formula.
=VLOOKUP(B6,Table,COLUMNS(Table),FALSE)

Utilizing COLUMNS function to Count Columns in Excel for Vlookup

  • Press Enter key.

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

Applying COLUMNS function to Count Columns in Excel for Vlookup

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

📕 Read More: 2 Handy Methods to Count Columns Until a Value Is Reached in Excel


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


Related Articles

(Visited 33 times, 1 visits today)
Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo