Home

Match different dimensions

%3CLINGO-SUB%20id%3D%22lingo-sub-534801%22%20slang%3D%22en-US%22%3EMatch%20different%20dimensions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534801%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EI'm%20having%20trouble%20to%20merge%20two%20excel%20sheets%20on%20Year%20and%20Country%20base.%20attached%20please%20see%20the%20excel%20file.%20On%20the%20first%20sheet%2C%20I%20would%20like%20to%20fill%20column%20D%20with%20the%20corresponding%20(both%20year%20and%20country%20code%20base)%20%22Total%20Eng%22%20data%20from%20the%20second%20sheet.%20Could%20someone%20please%20help%20me%20to%20write%20a%20formula%20to%20merge%20these%20data%3F%20I%20would%20appreciate%20that..%3C%2FP%3E%3CP%3Ethank%20you%20very%20much.%3C%2FP%3E%3CP%3ENelind%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-534801%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534898%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20different%20dimensions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334643%22%20target%3D%22_blank%22%3E%40Nelind%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Nelind%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Sheet2!%24D%242%3A%24D%2456%2CMATCH(1%2CINDEX((Sheet2!%24B%242%3A%24B%2456%3D%24A2)*(Sheet2!%24C%242%3A%24C%2456%3D%24B2)%2C0)%2C0))%2C%22no%20such%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-538947%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20different%20dimensions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-538947%22%20slang%3D%22en-US%22%3EA%20straightforward%20formula%20in%20D2%2C%20copied%20down%20rows%2C%20is%3A%3CBR%20%2F%3E%3DIFERROR(LOOKUP(PI()%2C%3CBR%20%2F%3E1%2F((Sheet2!B%242%3AB%2456%3DA2)*(Sheet2!C%242%3AC%2456%3DB2))%2C%3CBR%20%2F%3ESheet2!D%242%3AD%2456)%2C%3CBR%20%2F%3E0)%3C%2FLINGO-BODY%3E
Nelind
Occasional Visitor

Hello everyone,

I'm having trouble to merge two excel sheets on Year and Country base. attached please see the excel file. On the first sheet, I would like to fill column D with the corresponding (both year and country code base) "Total Eng" data from the second sheet. Could someone please help me to write a formula to merge these data? I would appreciate that..

thank you very much.

Nelind

2 Replies

@Nelind 

 

Hi Nelind,

 

That could be

=IFERROR(INDEX(Sheet2!$D$2:$D$56,MATCH(1,INDEX((Sheet2!$B$2:$B$56=$A2)*(Sheet2!$C$2:$C$56=$B2),0),0)),"no such")
A straightforward formula in D2, copied down rows, is:
=IFERROR(LOOKUP(PI(),
1/((Sheet2!B$2:B$56=A2)*(Sheet2!C$2:C$56=B2)),
Sheet2!D$2:D$56),
0)
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies