How to find column index number from table in Excel

In this article, we will learn How to extract the column index from the table in Excel.

Scenario:

Many times when working with long scattered data, we need to first clean data before working on it. It takes time and you just wanted to extract some queries. To extract data, you generally use the VLOOKUP function. But when we have long data with many column fields, then it gets messy because VLOOKUP function requires the correct column index as number, or else the formula returns error. Right there, below is the formula explanation for this kind of problem.

How to solve the problem?

For this we will use the MATCH function. If you haven't heard of this function, get used to it. It is an excel lookup function which returns the Index of the lookup value in the array. Here we need to get Index number of the column name. Then we will proceed to further step How to lookup values in tables using the MATCH function. Below is the Generic formula

Generic formula:

=MATCH(column_name, table_header, 0)

column_name : lookup value

table_header : table header array

0 : lookup for exact match

 

Example :

All of these might be confusing to understand. Let's understand the formula with explanation and example. Here we have a data table in Sheet 1 ($A$1:$U$9995). So we have the table header as A1:U1 (The first row of the table). 

Let's see the below formula to apply on the table.

Use the formula

=MATCH(C4,Sheet1!$A$1:$U$1,0)

Explanation:

  1. MATCH function looks up value in C4 cell "Order ID"
  2. Sheet1!$A$1:$U$1 is the lookup array argument.
  3. 0 argument is given to look up for exact match.

As you can see the column index of Order ID in table is 2nd. But it's very irritating to use the table header as full abbreviation, so we use the named range for the table header array. Learn more about named ranges here. The named range used for the table header (Sheet1!$A$1:$U$1) is "header". 

Use the formula.

=MATCH(C4,header,0)

Here the above snapshot explains two things. First is Column Index of State in table is 11 and second is named range "header" works fine. Copy the formula for remaining column names using Ctrl + D or dragging down from the right bottom edge the used cell.

Here we have all the column Index. Now we can use to give as input to the VLOOKUP function as shown below.

MATCH index in VLOOKUP function:

Now we have the solution for, how to get the column index of the table. We can use the formula as input to the VLOOKUP function. For example, we need the product name bought by the customer name "Pete Kriz". 

Use the formula:

=VLOOKUP(D10,Table,MATCH(E9,header,0),0)

Note: make sure lookup value in D10 (Pete Kriz) must be in the first column of the table.

As you can see the formula returns the product name from the customer name in table. Generally we don't use MATCH with VLOOKUP function because the lookup value must be in the first column, which hardly happens. So we use the combination of INDEX and MATCH function. Learn more about How to look up value using INDEX and MATCH function

Here are all the observational notes regarding using the formula. 

Notes:

  1. The formula works for text and numbers both.
  2. The function returns the #NA error if the lookup array argument to the MATCH function is not of the same length of the table array.
  3. The formula returns an error if lookup_value doesn't match the value in the table lookup_array.
  4. The function matches the exact value as the match type argument to the MATCH function is 0.
  5. Use the argument -1 for less than, 0 for exact match and 1 for the greater than the lookup match.
  6. The lookup values can be given as cell reference or directly using quote symbol ( " ) in the formula as arguments.

Hope you understood How to extract the column index from the table in Excel. Explore more articles on Excel lookup value & Excel 2019 functions here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

 

Related Articles

Use INDEX and MATCH to Lookup Value : INDEX & MATCH function to look up value as required.

SUM range with INDEX in Excel : Use INDEX function to find the SUM of the values as required.

How to use the INDEX function in Excel : Find the INDEX of an array using the INDEX function explained with an example.

How to use the MATCH function in Excel : Find the MATCH in the array using the INDEX value inside MATCH function explained with example.

How to use LOOKUP function in Excel : Find the lookup value in the array using the LOOKUP function explained with example.

Popular Articles:

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube