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))