Previous Topic Next Topic

Searching for a word in table

Solved/Closed
revs - Sep 2, 2011 at 08:20 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - 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



Related:

2 responses

Answer 1 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 3, 2011 at 11:33 PM
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
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Sep 8, 2011 at 10:00 AM
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