Searching for a word in table

[Solved/Closed]
Report
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
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



2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
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