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
a small macro will work(provided there are no gaps in data)
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))
Set cfind = r.Cells.Find(what:="money", lookat:=xlWhole)
If Not cfind Is Nothing Then Cells(j, 1) = "money"
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: