Lookup Text in Column & Return Adjacent Column Value

    Hi,


    I have a question about an array.


    I have one column with a list of words i.e.


    Column A
    [4 star hotels aruba]
    [4 star hotels in aruba]
    [5 star aruba hotels]
    [5 star aruba resorts]
    [5 star hotel aruba]
    [5 star hotel in aruba]


    Column B contains a list of destinations, I would like to know if there is a functions to look at each entry in column A and spit out the specific destination that is present in column A.


    My current forumla is below but it only spits out a "TRUE" if the destination is present. I would like for it to spit out the actual destination. Can anyone help?


    =ISNUMBER(LOOKUP(9.99E+307,SEARCH($J$3:$J$51,$A3)))

    Re: Lookup Text in Column & Return Adjacent Column Value


    vlookup would only work is it is exactly the same. The destinations are a part of the text string in column a. not sure how that would work using a vlookup...

    Re: Lookup Text in Column & Return Adjacent Column Value


    Ok.


    Column A(sample)
    aruba vacation
    vacations in aruba
    hawaii flights
    hawaii vacation
    aruba vacation


    Column B (list of destinations I want to search for in column A)
    aruba
    hawaii
    (list is 50+ destinations)


    Column C - Ouput (I need help finding a forumula to output this- currently forumla only outputs TRUE)
    aruba
    aruba
    hawaii
    hawaii
    aruba

    Re: Lookup Text in Column & Return Adjacent Column Value


    Next time provide a excel file as an example instead of a table in your post. This way developers dont have to waste time recreating your tables in excel for testring etc...

    Try the following formula:

    =INDEX(B$1:B$4,MATCH(TRUE,ISNUMBER(SEARCH(B$1:B$4,A1)),0))

    NOTE: its an array formula enter with ctrl-shift-enter

Participate now!

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

Register Yourself Login