searching for a word in table Solved/Closed

Question

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 



Configuration: Windows 7 / Internet Explorer 8.0

venkat1926 · Answer

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

TrowaD · Answer

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

Searching for a word in table

2 responses

Similar discussions

Subscribe To Our Newsletter!