Searching for a word in table [Solved/Closed]

- - Latest reply: TrowaD
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
- Sep 8, 2011 at 10:00 AM
Hello,
In excel, I have 5 columns of words (5 undividual word lists) and a row at the top that gives information on that word list. I want to use a function to search for a word, e.g. "money" and to find where this is in the table and then return the value in the first row of that column. The word "money" could appear more than once in the table but I want to return the value in the first column that it appears in. I couldn't work out how to do it with match and look up functions. Thanks



See more 

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
787
0
Thank you
a small macro will work(provided there are no gaps in data)

Sub test()
Dim r As Range, j As Integer, cfind As Range
For j = 1 To Range("A1").End(xlDown).Row
Set r = Range(Cells(j, 2), Cells(j, 2).End(xlToRight))
'MsgBox r.Address
Set cfind = r.Cells.Find(what:="money", lookat:=xlWhole)
If Not cfind Is Nothing Then Cells(j, 1) = "money"
Next j
End Sub
Posts
2570
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 3, 2019
377
0
Thank you
Hi revs,

In case you don't want to use a macro or the macro doesn't do exactly what you want (putting "money" to the left of your matrix instead of on top and repeating "money" more then once), here are formula's you could use:

You say you use 5 columns, I used A:E.

A1: =IF(ISFOUT(VLOOKUP("money",A2:A20,1,FALSE)),"",VLOOKUP("money",A2:A20,1,FALSE))
B1: =IF(A1="money","",IF(ISFOUT(VLOOKUP("money",B2:B20,1,FALSE)),"",VLOOKUP("money",B2:B20,1,FALSE)))
C1: =IF(OR(A1="money",B1="money"),"",IF(ISFOUT(VLOOKUP("money",C2:C20,1,FALSE)),"",VLOOKUP("money",C2:C20,1,FALSE)))
D1: =IF(OR(A1="money",B1="money",C1="money"),"",IF(ISFOUT(VLOOKUP("money",D2:D20,1,FALSE)),"",VLOOKUP("money",D2:D20,1,FALSE)))
E1: =IF(OR(A1="money",B1="money",C1="money",D1="money"),"",IF(ISFOUT(VLOOKUP("money",E2:E20,1,FALSE)),"",VLOOKUP("money",E2:E20,1,FALSE)))


Best regards,
Trowa