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

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(TR UE,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" wrote in message
ups.com...
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

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

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

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" wrote in message
oups.com...
Max - thank you so much! This is great and works a treat.

Regards
TC
London, UK