+ Reply to Thread
Results 1 to 5 of 5

Looking up data in a column, then returning values of respective row

  1. #1
    TC
    Guest

    Looking up data in a column, then returning values of respective row

    I'm trying to use Excel to create a basic rhyming dictionary. I have
    one worksheet for the query and results and another worksheet holding
    the word data. The data worksheet contains numerous rows of up to 30
    words each that all rhyme with each other, e.g. row 1 = brick, chick,
    flick; row 2 = dock, mock, clock; row 3 = career, veneer, appear (etc
    etc). My idea is that when the query term is found anywhere in the data
    sheet, all the words from the row it is found in are returned on the
    query/results sheet. For instance if the search term is "dock", the
    results in this example would be mock and clock. However I can't seem
    to get LOOKUP to return the values of all cells in the relevant row, or
    is that the wrong function for this?

    Many thanks
    TC


  2. #2
    Max
    Guest

    Re: Looking up data in a column, then returning values of respective row

    One play ..

    A sample construct is available at:
    http://www.savefile.com/files/9500795
    Rhyming dictionary.xls

    Assume the word data is in sheet: X,
    cols A to AD, data from row1 down to row100 (say)

    In sheet: X,

    Insert a new col A (the word data will now be in cols B to AE)

    Then put in A1: =MATCH(Query!$A$1,B1:AE1,0)
    Copy A1 down to A100

    In sheet: Query,

    A1 will house the input for the query word

    Select A2:AD2, put in the formula bar,
    and array-enter the formula (Press CTRL+SHIFT+ENTER):

    =IF(TRIM(A1)="","",IF(OFFSET(X!$B$1:$AE$1,MATCH(TRUE,ISNUMBER(X!$A$1:$A$100)
    ,0)-1,)=0,"",OFFSET(X!$B$1:$AE$1,MATCH(TRUE,ISNUMBER(X!$A$1:$A$100),0)-1,)))

    A2:AD2 will return the required results,
    i.e. the row which contains the word in sheet: X

    Adapt to suit ..
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "TC" <[email protected]> wrote in message
    news:[email protected]...
    > I'm trying to use Excel to create a basic rhyming dictionary. I have
    > one worksheet for the query and results and another worksheet holding
    > the word data. The data worksheet contains numerous rows of up to 30
    > words each that all rhyme with each other, e.g. row 1 = brick, chick,
    > flick; row 2 = dock, mock, clock; row 3 = career, veneer, appear (etc
    > etc). My idea is that when the query term is found anywhere in the data
    > sheet, all the words from the row it is found in are returned on the
    > query/results sheet. For instance if the search term is "dock", the
    > results in this example would be mock and clock. However I can't seem
    > to get LOOKUP to return the values of all cells in the relevant row, or
    > is that the wrong function for this?
    >
    > Many thanks
    > TC
    >




  3. #3
    Stefi
    Guest

    RE: Looking up data in a column, then returning values of respective r

    I think this can be solved only with a UDF! Voilá:

    Function RhymeFind(rhymeto)
    hitrow = 0
    On Error Resume Next
    hitrow = Worksheets("Sheet2").Cells.Find(What:=rhymeto, After:=[A1],
    LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
    MatchCase:= _
    False, SearchFormat:=False).Row
    If hitrow > 0 Then
    returnstr = ""
    r = 1
    Do While Not IsEmpty(Worksheets("Sheet2").Cells(hitrow, r))
    returnstr = returnstr & Worksheets("Sheet2").Cells(hitrow, r) &
    ","
    r = r + 1
    Loop
    RhymeFind = Left(returnstr, Len(returnstr) - 1)
    Else
    RhymeFind = "No hit!"
    End If
    End Function

    Usage:
    Enter in cell A1 the word you search rhymes to, then in B2 enter
    =Rhymefind(A1)


    Note, that in sheet2 the words are in separate cells!


    Regards,
    Stefi

    „TC” ezt *rta:

    > I'm trying to use Excel to create a basic rhyming dictionary. I have
    > one worksheet for the query and results and another worksheet holding
    > the word data. The data worksheet contains numerous rows of up to 30
    > words each that all rhyme with each other, e.g. row 1 = brick, chick,
    > flick; row 2 = dock, mock, clock; row 3 = career, veneer, appear (etc
    > etc). My idea is that when the query term is found anywhere in the data
    > sheet, all the words from the row it is found in are returned on the
    > query/results sheet. For instance if the search term is "dock", the
    > results in this example would be mock and clock. However I can't seem
    > to get LOOKUP to return the values of all cells in the relevant row, or
    > is that the wrong function for this?
    >
    > Many thanks
    > TC
    >
    >


  4. #4
    TC
    Guest

    Re: Looking up data in a column, then returning values of respective row

    Max - thank you so much! This is great and works a treat.

    Regards
    TC
    London, UK

    Max wrote:

    > One play ..
    >
    > A sample construct is available at:
    > http://www.savefile.com/files/9500795
    > Rhyming dictionary.xls



  5. #5
    Max
    Guest

    Re: Looking up data in a column, then returning values of respective row

    You're welcome, TC !
    Thanks for the feedback ..
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "TC" <[email protected]> wrote in message
    news:[email protected]...
    > Max - thank you so much! This is great and works a treat.
    >
    > Regards
    > TC
    > London, UK




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1