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