Search for value and return text in column header

Re: Search for value and return text in column header

Hello nutritionkiwi,

Welcome to Ozgrid. We're glad to have you on board, however, please note the following regarding thread titles:

Thread titles are used in searching the forum, therefore, it is vital they be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words. That is, your title use as search terms would return relevant results.

• The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
• The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (e.g. slashes, commas, colons, etc)
• The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different

[COLOR="darkred"]Please note the change to your title, which is based on the objective stated in your thread.[/COLOR]
-------------------------------------------------

Using your sample data with the values starting in cell A1, try this:

Option Explicit


Sub Find_County()


    Dim rFound As Range, strName As String, strZip As String
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    On Error Resume Next
    
    strZip = Application.InputBox("Enter the ZIP code to find", Type:=2)
    
    If strZip = vbNullString Then
        MsgBox "You entered and invalid entry -- cancelling search"
        Exit Sub
    End If
    
    With Sheet1
        Set rFound = .Cells.Find(what:=strZip, after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
        
        If Not rFound Is Nothing Then
            strName = .Cells(1, rFound.Column).Text
            MsgBox strName
        End If
    End With
        
    Set rFound = Nothing
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

Note: Sheet references are sheet code names -- not worksheet (tab) names -- adjust as required.

To add the Macro:

Press Alt + F11 to open the VB Editor
Menu > Insert > Module (not a Class Module)
Copy and paste this code into the module
Adjust any sheet or range references as required
Press Alt + Q to close VB Editor

To run the macro press Alt + F8, select the macro and run it.

Re: Search for value and return text in column header

Assuming your table is in A1:D5 then with a specific zip code in F2 try this "array formula" to get the relevant header

=INDEX(A1:D1,MIN(IF(A2:D5=F2,COLUMN(A2:D5)-COLUMN(A2)+1)))

Formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar