Search for value and return text in column header

  • I have a list of zip codes spread across several columns and organized by county:


    Boone | Cook | DeKalb | Dupage
    60033 | 60004 | 60111 | 60007
    60135 | 60005 | 60112 | 60101
    60145 | 60007 | 60115 | 60103
    60146 | 60008 | 60129 | 60106


    My goal is to search the list of zip codes organized across multiple columns [where the lookup_value is a zip code], and then return the name of the county that that zip code is located under.


    Can anybody help?

  • 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:


    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!