'/**************************************************************************
'* Function: LOOKUPH                                                       *
'*                                                                         *
'* Author: Ruben Alvarez                                                   *
'* Date: 01/07/2011                                                        *
'*                                                                         *
'* Description: Search a reference text into any row in a range and        *
'*              returns the value in the same column in the specified row  *
'**************************************************************************/

Public Function LOOKUPH(RefText As String, LookupRange As String, LookupRow As Integer, ReturnRow As Integer) As String
           
    Dim s As Variant
    s = Split(LookupRange, ":", , vbTextCompare)
    If UBound(s) = 1 Then
        
        start_col_number = CNumber(CStr(s(0)))
        final_col_number = CNumber(CStr(s(1)))
        start_col_letter = CLetter(CLng(start_col_number))
        final_col_letter = CLetter(CLng(final_col_number))
                
        start_row_number = CInt(Right(s(0), Len(s(0)) - Len(start_col_letter)))
        final_row_number = CInt(Right(s(1), Len(s(1)) - Len(final_col_letter)))
    Else
        LOOKUPH = "#ERROR#"
        Exit Function
    End If
    
    For lookup_col = start_col_number To final_col_number
        If Cells(start_row_number + LookupRow - 1, lookup_col).Text = RefText Then
            LOOKUPH = Cells(start_row_number + ReturnRow - 1, lookup_col).Value
        End If
    Next lookup_col
    
End Function

Public Function CLetter(v As Long) As String
    CLetter = Left(Cells(1, v).Address(1, 0), InStr(1, Cells(1, v).Address(1, 0), "$") - 1)
End Function
Public Function CNumber(v As String) As Integer
    CNumber = Range(v & "1").Column
End Function