Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count unique text values with criteria
  • Vlookup Examples in Excel
  • Approximate match with multiple criteria in Excel
  • Count rows that contain specific values in Excel
  • How to use Excel MATCH Function

Data Analysis Examples

  • Conditional Formatting Icon Sets Examples in Excel
  • Excel Pie Chart
  • Reverse List in Excel
  • Example of COUNTIFS with variable table column in Excel
  • Move chart to a separate worksheet in Excel

Data Validation Examples

  • Excel Data validation require unique number
  • Excel Data validation exists in list
  • Excel Data validation no punctuation
  • Excel Data validation specific characters only
  • Excel Data validation must contain specific text

Get column index in Excel Table

by

To get the index of a column in an Excel Table, you can use the MATCH function. See example below:

Formula

=MATCH(name,Table[#Headers],0)

Explanation

In the example shown, the formula in I4 is:

=MATCH(H4,Table1[#Headers],0)

When the formula is copied down, it returns an index for each column listed in column H. Getting an index like this is useful when you want to refer to table columns by index in other formulas, like VLOOKUP, INDEX and MATCH, etc.

Worked Example:   How to count table rows in Excel

How this formula works

This is a standard MATCH formula where the lookup values come from column H, the array is the headers in Table1, and match type is zero, to force an exact match.

Worked Example:   How to calculate two-way lookup VLOOKUP in Excel Table

The only trick to the formula is the use of a structured reference to return a range for the table headers to the MATCH function:

Table1[#Headers]

The nice thing about this reference is that it will automatically adjust to any changes in the table. Even when columns are added or removed, the reference will continue to return the correct range.

Worked Example:   Categorize text with keywords in Excel

Post navigation

Previous Post:

How to calculate average last N values in a table in Excel

Next Post:

How to create running total in an Excel Table

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Normalize text by removing punctuations, extra spaces and more in Excel
  • Get first name from name with comma — Manipulating NAMES in Excel
  • NUMBERVALUE function: Description, Usage, Syntax, Examples and Explanation
  • How to strip protocol and trailing slash from URL in Excel
  • Remove text by variable position in a cell in Excel
  • How to calculate next scheduled event in Excel
  • Convert decimal hours to Excel time
  • List holidays between two dates in Excel
  • Calculate days remaining in Excel
  • Calculate number of hours between two times in Excel
  • NPV function: Description, Usage, Syntax, Examples and Explanation
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest for given period in Excel
  • How to calculate simple interest in Excel
  • FV function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles