How can I search a range of text and return value of text from first column?

mpcassid 

I repeated solution which Detlef_Lewin suggested, only modified the code a bit.

Perhaps you are not familiar with Power Query, when from scratch.

If you are on Excel for Mac that solution doesn't work. If on Excel 2016 or later, Power Query is built into Excel. If earlier version (2010, 2013) you need to download from Microsoft Downloads and install free Power Query add-in.

With your actual file - you have at least two options. First, which is preferable convert your source data into Excel Table. Stay within the range (or select it), Ctrl+T, check your table has headers, Enter. Rename the table as suitable, in my sample tblSignUp.

Second option use your data as named range, Select it, better with some gap on future expansion, with one-two empty columns to the right and with empty rows down. In Formulas->Name Manager give the name to the range, let say rngSignUp.

Create new blank query like Data->Get Data->From Other Sources->Blank Query. Power Query editor will be opened, here find Home->Advanced Editor. Copy your variant of code from here below, or from attached file. Ctrl+A and paste this code into Advanced Editor window -> Done. Be sure query works, after that Home->Close and Load To into Excel sheet.

Above is one variant of steps to perform, that could other ways, just keep an idea.

Code to the table:

let
    Source = Excel.CurrentWorkbook(){[Name="tblSignUp"]}[Content],
    UnpivotOtherColumns = Table.UnpivotOtherColumns(
        Source,
        {"Name"}, "Attribute", "Value"
    ),
    GroupRows = Table.Group(
        UnpivotOtherColumns,
        {"Value"}, {{"Names", each [Name]}}
    ),
    Result = Table.FromRows(
        List.Zip(GroupRows[Names]),GroupRows[Value]
    )
in
    Result

Code for the range:

let
    Source = Excel.CurrentWorkbook(),
    FilteredOnRange = Table.SelectRows(Source, each ([Name] = "rngSignUp")),
    KeepTable = Table.SelectColumns(FilteredOnRange,{"Content"}),
    ColumnNames = Table.ColumnNames(KeepTable[Content]{0}),
    ExpandRange = Table.ExpandTableColumn(
        KeepTable,
        "Content",
        ColumnNames,
        ColumnNames
    ),
    ExamsToHeaders = Table.PromoteHeaders(
        ExpandRange,
        [PromoteAllScalars=true]
    ),
    NewColumnNames = Table.ColumnNames(ExamsToHeaders),
    ColumnsToKeep = List.RemoveMatchingItems(
        NewColumnNames,ColumnNames),
    KeepExamsColumns = Table.SelectColumns(
        ExamsToHeaders,
        ColumnsToKeep
    ),
    RemoveEmptyRows = Table.SelectRows(
        KeepExamsColumns,
        each ([Name] <> null)
    ),
    UnpivotOtherColumns = Table.UnpivotOtherColumns(
        RemoveEmptyRows,
        {"Name"}, "Attribute", "Value"
    ),
    GroupRows = Table.Group(
        UnpivotOtherColumns,
        {"Value"}, {{"Names", each [Name]}}
    ),
    Result = Table.FromRows(
        List.Zip(GroupRows[Names]),GroupRows[Value]
    )
in
    Result

Alternatively you may not care about the code, just take Detlef_Lewin variant and repeat all steps from his query on your actual data using Power Query user interface only. Here you shall be careful with number and names of the columns.