Please Help to solve this query

[suresh123]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl81, width: 90"]Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="class: xl81, width: 150"]Particular[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl81, width: 58"]Rank[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl81, width: 90"]Mr Joy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="class: xl81, width: 150"]why did Mr A Hate Mr C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="class: xl81, width: 150"]who is Mr Joy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="class: xl81, width: 150"]were is Mr X going[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="class: xl81, width: 150"]why did Mr A Hate Mr C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="class: xl81, width: 150"]Mr x Is going to Party of mr c[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

As data shown above;please help
i want to search Mr Joy in the " particular" row and Give the Result of "Rank" in the other row
For eg search/find Mr Joy from particular Above and give the result as "2" in the next 4th row
HINT vlookup along with search/find option
or suggest if any other

 

[sijpie]

I just wrote a function for someone else to find the row number of a text in a searc column.

Copy this function in a VBA macro module. Then you can use the function on your spreadsheet.


<font face=Calibri><SPAN style="color:#007F00">'=======================================================================</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> FindKeywordRow(<SPAN style="color:#00007F">ByRef</SPAN> vKeyW <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, <SPAN style="color:#00007F">ByRef</SPAN> rFindRange <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#007F00">' function that gives the row number of vKeyW in range rFindRange.</SPAN><br><SPAN style="color:#007F00">' rFindRange can either be given as the full range to search or _<br>  as a single cell of the column to search. _<br>  vKeyW can be part of a text in a cell in the search column</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' If used as a worksheet function then vKeyW can either be given as _<br>  a range or as text.</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Returns 0 if keyword not found</SPAN><br><SPAN style="color:#007F00">'=======================================================================</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">If</SPAN> rFindRange.Cells.Count = 1 <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#007F00">' single cell so convert to the column</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rFindRange = rFindRange.Parent.Columns(rFindRange.Column)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rFound = rFindRange.Find(vKeyW, Lookat:=xlPart)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        FindKeywordRow = rFound.Row<br>    <SPAN style="color:#00007F">Else</SPAN> <SPAN style="color:#007F00">' not found</SPAN><br>        FindKeywordRow = 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> FindKeywordRow_Help() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>FindKeywordRow_Help = "Function that gives the row number of vKeyW in range rFindRange." & vbCrLf & _<br>    "1stParameter: text or cell with text to find" & vbCrLf & _<br>    "2ndParameter: Either First cell of column or the total range to search in." & vbCrLf & _<br>    "Returns 0 if keyword not found"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><br></FONT>


For you to use it, and find the value 2 would be:
=INDEX(C:C,FindKeywordRow(A3,B1))