How to return data from another sheet when 2 columns matches

%3CLINGO-SUB%20id%3D%22lingo-sub-1834842%22%20slang%3D%22en-US%22%3EHow%20to%20return%20data%20from%20another%20sheet%20when%202%20columns%20matches%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1834842%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20spreadsheet%2C%20I%20have%202%20sheets.%20The%203%20columns%20I%20have%20in%20both%20sheets%20are%20PO%23%2C%20Inv%23%2C%20and%20Charges.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20want%20to%20do.%20I%20want%20the%20Charges%20from%20sheet%202%20to%20automatically%20load%20into%20sheet%201%20when%20PO%23%20and%20Inv%23%20matches%20in%20both%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22SHEET1.png%22%20style%3D%22width%3A%20444px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230232i8003A8609392A20A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SHEET1.png%22%20alt%3D%22SHEET1.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22SHEET2.png%22%20style%3D%22width%3A%20409px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230233i2BC2D34836F3CDA7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SHEET2.png%22%20alt%3D%22SHEET2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1834842%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1835019%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20return%20data%20from%20another%20sheet%20when%202%20columns%20matches%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1835019%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F850940%22%20target%3D%22_blank%22%3E%40ybotL%3C%2FA%3E%26nbsp%3B%20try%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMPRODUCT('SHEET2'!C%3AC*(A2%3D'SHEET2'!A%3AA)*(B2%3D'SHEET2'!B%3AB))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1836490%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20return%20data%20from%20another%20sheet%20when%202%20columns%20matches%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1836490%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F850940%22%20target%3D%22_blank%22%3E%40ybotL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EYou%20need%20an%20array%20(CSE)%26nbsp%3B%20formula%2C%20fixes%20the%20issue%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1604046405272.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230371iAC326171CF361554%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Rajesh-S_0-1604046405272.png%22%20alt%3D%22Rajesh-S_0-1604046405272.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EEnter%20this%20formula%20in%20Sheet1's%26nbsp%3B%20cell%20D25%3A%3C%2FLI%3E%3C%2FUL%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7B%3DIFERROR(INDEX(Sheet2!%24C%2433%3A%24C%2439%2CMATCH(Sheet1!A25%26amp%3BSheet1!B25%2CSheet2!%24A%2433%3A%24A%2439%26amp%3BSheet2!%24B%2433%3A%24B%2439%2C0))%2C%22%22)%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EFinish%20the%20formula%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%26nbsp%3B%3C%2FSTRONG%3E%26amp%3B%20fill%20down.%3C%2FLI%3E%3CLI%3EAdjust%20cell%20references%20in%20the%20formula%20as%20needed.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3E****%26nbsp%3B%3C%2FSTRONG%3E%3CFONT%20size%3D%221%202%203%204%205%206%207%22%20color%3D%22%230000FF%22%3E%3CEM%3EIf%20you%20find%20this%20works%20for%20you%20then%20you%20may%20marks%20it%20as%20Best%20Answer%20as%20well%20as%26nbsp%3B%20Like.%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1836684%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20return%20data%20from%20another%20sheet%20when%202%20columns%20matches%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1836684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F850940%22%20target%3D%22_blank%22%3E%40ybotL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20you%20are%20transferring%20data%20from%20Table2%20on%20Sheet2%20to%20Table1%2C%20SUMIFS%20or%20XLOOKUP%20could%20be%20used%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20SUMIFS(Table2%5BCHARGES%5D%2C%20%0A%20%20Table2%5BPO%5D%2C%5B%40PO%5D%2C%0A%20%20Table2%5BINV%5D%2C%5B%40INV%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20XLOOKUP(%201%2C%20%0A%20%20(Table2%5BPO%5D%3D%5B%40PO%5D)*(Table2%5BINV%5D%3D%5B%40INV%5D)%2C%20%0A%20%20Table2%5BCHARGES%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

In my spreadsheet, I have 2 sheets. The 3 columns I have in both sheets are PO#, Inv#, and Charges.

 

This is what I want to do. I want the Charges from sheet 2 to automatically load into sheet 1 when PO# and Inv# matches in both sheets.

 

SHEET1.pngSHEET2.png

3 Replies

@ybotL  try

=SUMPRODUCT('SHEET2'!C:C*(A2='SHEET2'!A:A)*(B2='SHEET2'!B:B))

@ybotL 

 

You need an array (CSE)  formula, fixes the issue:

 

Rajesh-S_0-1604046405272.png

 

  • Enter this formula in Sheet1's  cell D25:
{=IFERROR(INDEX(Sheet2!$C$33:$C$39,MATCH(Sheet1!A25&Sheet1!B25,Sheet2!$A$33:$A$39&Sheet2!$B$33:$B$39,0)),"")}

 

N.B. 

  • Finish the formula with Ctrl+Shift+Enter & fill down.
  • Adjust cell references in the formula as needed.

**** If you find this works for you then you may marks it as Best Answer as well as  Like.

 

@ybotL 

Assuming you are transferring data from Table2 on Sheet2 to Table1, SUMIFS or XLOOKUP could be used

= SUMIFS(Table2[CHARGES], 
  Table2[PO],[@PO],
  Table2[INV],[@INV])

or

= XLOOKUP( 1, 
  (Table2[PO]=[@PO])*(Table2[INV]=[@INV]), 
  Table2[CHARGES])