Forum Discussion

ybotL's avatar
ybotL
Copper Contributor
Oct 29, 2020

How to return data from another sheet when 2 columns matches

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.

 

excel

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 30, 2020

    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])
  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Steel Contributor
    Oct 30, 2020

    ybotL 

     

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

     

     

    • 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.