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

Lookup and Reference Examples

  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get first column number in range in Excel
  • Left Lookup in Excel
  • How to use Excel MATCH Function
  • Excel Advanced Lookup using Index and Match Functions

Data Analysis Examples

  • Understanding Pivot Tables in Excel
  • Excel Line Chart
  • How To Perform and Interpret Regression Analysis in Excel
  • Everything about Charts in Excel
  • Understanding Anova in Excel

Data Validation Examples

  • Excel Data validation require unique number
  • Excel Data validation exists in list
  • Excel Data validation only dates between
  • Excel Data validation no punctuation
  • Excel Data validation allow uppercase only

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:   Calculate Conditional Percentile 'IF' in table 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:   Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

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:   INDEX and MATCH with multiple criteria 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
  • Remove file extension from filename in Excel
  • How to convert text string to array in Excel
  • Remove leading and trailing spaces from text in one or more cells in Excel
  • Get position of 2nd 3rd and more instance of character in Excel
  • How to compare two text strings in Excel
  • Pad week numbers with zeros in Excel
  • Display Days in month in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate Net Present Value (NPV) in Excel
  • AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • DDB function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles