• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel index Match formula

Malleshg24

New Member
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
 

Attachments

  • Extract Captain Name.xlsx
    14.5 KB · Views: 10
C2 of the Input File sheet, array-entered (meaning commit the formula to the sheet using Ctrl+Shift+Enter, not just Enter):
Code:
=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.
 
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.
 

Attachments

  • Extract Captain Name.xlsx
    18 KB · Views: 8
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))
 
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,
 
Back
Top