excel index Match formula

[Malleshg24]

Hi Team,

I am looking for excel formula/VBA Code for my attached data,
I want to fill column C and for that I am using vlookup.

my actually Data is different just replacated some dummy data.

I am getting Result with the below formula, however I have taken the helper column in Mapping sheet.
=VLOOKUP(A2&B2,'Master Template'!C:D,2,0)


Is there any other formula to get same result without helper column in Mapping sheet.

Thanks in advance
mg

 

[p45cal]

C2 of the Input File sheet, array-entered (meaning commit the formula to the sheet using Ctrl+Shift+Enter, not just Enter):

=INDEX(Mapping!$C$1:$C$40,MATCH($A2&$B2,Mapping!$A$1:$A$40&Mapping!$B$1:$B$40,0))

copy down,
not forgetting to delete your existing column C from the Mapping sheet.

 

[Peter Bartholomew]

LOOKUP would do it
= LOOKUP( 1, 1 / (Mapping[Name]=[@Name]) / (Mapping[Country]=[@Country]), Mapping[Captain] )
XLOOKUP would also work if you are using Office 365.

Note: I have also introduced tables but that is not necessary. It is simply that I have issues following formulas based on direct referencing by location.

 

[Juniad]

U can try this one too in C3 and drag down
=INDEX(Mapping!$D$2:$D$19,MATCH(1,INDEX((Mapping!$A$2:$A$19=A2)*(Mapping!$B$2:$B$19=B2),),0))

 

[Khalid NGO]

Hi,

Recently learned from Bill Jelen (MrExcel) that referencing the array like A2:A10=A2 in formula takes more calculation time, even if it is used with new Excel Calculation Engine and XLOOKUP, an alternate approach:

=INDEX(Mapping!$D$2:$D$19,MATCH(A2&B2,INDEX(Mapping!$A$2:$A$19&Mapping!$B$2:$B$19,),0),)

Regards,