Excel VBA: Find a Column Number by Header Name in a Row

To find a column by its header in Excel VBA, search the header row with Range.Find and read the matching cell’s Column property. The function below returns the worksheet column number, or 0 if it cannot find an exact header match.

A reusable VBA function

Pass the function a one-row range containing the headers and the text to look for. It searches for a whole-cell match without regard to capitalization, and it sets the important Find options explicitly so results do not depend on settings left over from a previous Find operation.

Public Function FindHeaderColumn(ByVal headerRange As Range, ByVal headerName As String) As Long
    Dim foundCell As Range

    ' Return 0 for an invalid range or an empty search term.
    If headerRange Is Nothing Then Exit Function
    If headerRange.Rows.Count <> 1 Then Exit Function
    If Len(headerName) = 0 Then Exit Function

    ' Search for the complete header text in this one-row range.
    Set foundCell = headerRange.Find( _
        What:=headerName, _
        After:=headerRange.Cells(headerRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If Not foundCell Is Nothing Then
        FindHeaderColumn = foundCell.Column
    End If
End Function

Put the function in a standard VBA module: in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code. On a Mac, use Tools > Macro > Visual Basic Editor, or the Visual Basic Editor command if it is available on the ribbon; keyboard shortcuts can differ by keyboard and Excel version. The function can then be called from another macro in the same workbook project.

Call the function from a macro

For example, suppose the worksheet named Data has headers in A1:Z1 and one of them is Invoice Date. This macro shows the column number when found and a clear message when the label is absent.

Sub ExampleFindHeader()
    Dim ws As Worksheet
    Dim columnNumber As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    columnNumber = FindHeaderColumn(ws.Range("A1:Z1"), "Invoice Date")

    If columnNumber = 0 Then
        MsgBox "Header not found."
    Else
        MsgBox "Invoice Date is in worksheet column " & columnNumber
    End If
End Sub

If Invoice Date is in column D, the function returns 4, because Excel numbers worksheet columns from A = 1 through Z = 26. Notice that the return value is an absolute worksheet column number, not the position inside the range you searched. If your search range starts at column C and the match is in column D, the function still returns 4.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, even if the macro only appears to make a small change. This example only reads cells and displays a message, but that safeguard matters when you adapt the code to edit or delete data.

How Range.Find locates a header

Find searches cells in a specified range and returns a Range object for a match. The code asks for xlWhole, so a cell containing Invoice Date matches that search term, while Invoice Date Old does not. xlValues tells Excel to search the cell values rather than the underlying formula text. That is generally appropriate for a visible header, including a header whose displayed value comes from a formula.

Excel’s Find options can persist between calls and can be influenced by settings used in the Find dialog. Leaving options implicit can therefore cause a macro to search for partial matches, look in formulas, or behave differently than intended. Supplying LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, and SearchFormat makes the search behavior much clearer and more repeatable.

The search range is limited to one row by design. Searching only the header cells avoids matching a data value lower in the worksheet and makes the function’s purpose explicit. For a table with headers on row 3, pass that row’s header range, such as ws.Range("A3:Z3"), rather than changing the function.

Choose a range and interpret the result

Choose a range that covers the full set of possible headers but not unrelated cells. A fixed range such as A1:Z1 is easy to read and works well when the worksheet layout has a known maximum width. If columns may be added, update the range or determine its width from the worksheet’s structure. Searching all 16,384 columns in a modern Excel worksheet is possible, but an intentionally scoped range communicates the expected layout and avoids searching unnecessary cells.

Known layout: Pass a range such as ws.Range("A1:Z1") when the headers are always within those columns.
Excel Table: Use the table’s header row when the data is structured as a ListObject. This can be more resilient than hard-coding a broad worksheet range.
Missing label: Treat 0 as “not found.” Do not use it as a worksheet column number; valid worksheet columns start at 1.
Relative position needed: Convert the absolute result to an index within the searched range with foundCell.Column - headerRange.Column + 1.

For example, if the searched range is D1:H1 and the match is in F1, the function returns 6 because F is the sixth worksheet column. Its position inside D1:H1 is 3. These values answer different questions: use the absolute number with Cells(rowNumber, columnNumber) on the worksheet, and the relative position when indexing a list or array built from just the searched range.

Exact matches, partial matches, and capitalization

The example uses LookAt:=xlWhole because headers are usually labels that should match as a complete cell. If you deliberately want a partial match, change this setting to xlPart. Searching for Date could then match Invoice Date, but it may also match Order Date or Date Entered. Partial matching is convenient when labels vary, but it increases the chance of finding the wrong column.

MatchCase:=False makes capitalization irrelevant: invoice date and Invoice Date are treated as the same search term. Set it to True only if capitalization is meaningful in your workbook. The search does not automatically remove leading or trailing spaces. A header containing Invoice Date followed by a space may therefore fail an exact search. If imported labels are inconsistent, normalize the header cells or add deliberate cleanup logic rather than silently changing the requested search term.

Duplicate headers are another important case. Find returns one matching cell, not a list of all matching cells. With the supplied starting point and direction, a repeated label can lead to the first occurrence in the search cycle. If duplicate names are possible, make the headers unique or write code that continues searching and checks each result. Do not assume that a match is unique merely because the function returned a column number.

Alternatives: Match and a loop

For a simple one-row lookup, VBA’s Application.Match can return a position within a range. It is useful when you need that relative position and are comfortable handling an error result when the header is missing. Unlike the function above, Match’s returned position is relative to the lookup range, so it is not automatically the worksheet’s column number. Also, an exact Match search generally requires the lookup range to be ordered only when using approximate matching; use exact-match mode for header labels.

A loop is another option when you need custom rules, such as trimming spaces, comparing case-sensitively, or collecting duplicate headers. For example, a loop can inspect each cell’s Value2 and compare it with a normalized search string. That gives you control over cleanup, but you must define the behavior yourself. For a straightforward exact header lookup, Find is concise and makes its matching options visible in one place.

Use the method that matches the result you need: Find returns the matched cell and its absolute column is easy to read; Match naturally gives a position within a range; a loop offers the most control. These methods are alternatives, not interchangeable return values. Check whether your next operation expects an absolute worksheet column or a range-relative index before passing the result onward.

Common problems and how to diagnose them

The result is 0: Check the worksheet, header row, range boundaries, spelling, and spaces. Confirm that the requested label is actually within the range passed to the function.
A longer label matched unexpectedly: Verify that LookAt is set to xlWhole, not xlPart.
The wrong duplicate was returned: Check for repeated header text. Find returns a match, not a uniqueness guarantee.
A formula-generated header is not found: The example searches values with xlValues. If you need to search the literal formula text instead, use xlFormulas, bearing in mind that the formula itself will not usually be the same text as the displayed header.
The function reports the wrong number: Remember that Column is the worksheet’s absolute column number. A match in F is 6 even when F is only the third cell in a D:H search range.
The macro cannot find the sheet: Check the worksheet tab name in Worksheets("Data"). The sheet name must match, including spaces; the workbook reference determines which file Excel searches.

Headers in merged cells can also be awkward. The text is stored in the upper-left cell of a merged area, while the other cells in the merge may be empty. Search the cell that actually contains the text, and consider avoiding merged header cells in data layouts intended for sorting, filtering, or automated processing.

Running and adapting the code safely

Macros must be stored in a macro-enabled workbook, typically an XLSM file, if you want the VBA code to remain in that file. Save the workbook as an Excel Macro-Enabled Workbook when prompted. Do not enable macros in an unfamiliar file just to run a lookup: use Excel’s security prompts carefully and run code only from a source you trust.

The sample uses ThisWorkbook, which refers to the workbook containing the code. That is often safer than relying on ActiveWorkbook, since the active workbook can change while a macro runs. It also refers to the worksheet by its tab name, making the target explicit. If you place the function in a different workbook or add-in, confirm that the workbook and worksheet references point to the data you intend to search.

To use the returned number to read a value, combine it with a row number after checking that it is not 0. For example, if the header is found in column 6 and the record of interest is on row 2, the worksheet cell is F2. Keep the not-found check: attempting to use 0 as a column index causes an error. For edits, deletions, or multi-step automation, test on a copy first and consider adding additional checks for blank headers and duplicate names.

Frequently Asked Questions

How do I get the column letter instead of the number?
The function returns the numeric column, which is usually easier to use in VBA. To display a column letter, you can derive it from the matching cell’s address, but avoid converting to letters if the next step can use Cells(rowNumber, columnNumber) directly.

Does this search find headers regardless of uppercase or lowercase?
Yes. The sample sets MatchCase:=False, so capitalization does not matter. Set it to True if case-sensitive matching is required.

Why does the function return 0?
It returns 0 when the search term is empty, the supplied range is not one row, or no exact match is found. Check that the header text and range are correct, and look for leading or trailing spaces.

Can I search a header row other than row 1?
Yes. Pass the appropriate row range, such as ws.Range("A3:Z3") for headers in row 3. The function does not assume that headers are on the first row.

What if two columns have the same header?
The function returns one matching column; it does not report every duplicate. Make labels unique or adapt the search to continue through the range and collect every matching cell before choosing which column to use.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.