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]

26050

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