vba Help - extract Matching value

[malleshg24]

Hi Team,

I have attached input file , I am looking excel formula or code in vba/both.

In Input sheets Column A is player Name,
Column B - Country Name
Column C - Captain Name


I want to fill Column C, captain Name by checking in Master Template.
Expected result is in Column J of Input sheets.


Thanks in advance for your help.




Regards,
mg

[Paul_Hossler]

Capture.JPG

The VLookup() is pretty basic

In C2 and copy down

=VLOOKUP(A2,'Master Template'!$A:$C,3,FALSE)

[malleshg24]

Hi Paul,

Thanks for your help, Actually my data is little different,
my lookup value should be always a2&b2 concatenation and so on ....
=VLOOKUP(A2&B2,'Master Template'!CD,2,0) I get result with this, if I merge column A and B in C Column Master Template sheets.

with helper column in Master Template I get the result.

is it possible to get result without helper column.


Regards,
mg

[Paul_Hossler]

Maybe you need to update your attachment

The is no data in Col D in Master Template, and there is no helper column built from other columns (using formulas)

Col A on Input is used to look for a equal in Col A on template

Are you saying that you want to match NAME+COUNTRY and get CAPTAIN?????????????????

Option Explicit


Function VLookup2(Input1 As Variant, Input2 As Variant, DataLookup As Range, DataReturn As Range) As Variant
    Dim i As Long, rowLast As Long
    Dim i1 As Variant, i2 As Variant
    
    VLookup2 = CVErr(xlErrNA)
    On Error GoTo NiceExit
    
    With DataReturn
        rowLast = .Parent.Cells(.Rows.Count, .Column).End(xlUp).Row
    End With
    
    i1 = LCase(Input1)
    i2 = LCase(Input2)
    
    For i = 1 To rowLast
        If i1 = LCase(DataLookup.Cells(i, 1).Value) And i2 = LCase(DataLookup.Cells(i, 2).Value) Then
            VLookup2 = DataReturn.Cells(i, 1).Value
            Exit Function
        End If
    Next i


    Exit Function


NiceExit:


End Function