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.
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.
- 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
👉 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.
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.
- 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
👉 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.
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.
- Count One Column If Another Column Meets Criteria in Excel
- One Column Count If Another Column Meets Criteria in Excel