Forum Discussion

Ellen Class's avatar
Ellen Class
Copper Contributor
Jun 18, 2018

Match names between two sheets and return value of a cell in the row

Hi there:  

 

I am tracking forecast and actuals for various projects and vendors (example attached).  I'm looking for a formula to compare the projectname, services co. name, month and year on different rows and then subtract the amounts in column N (net amount due) from column P (forecast amt.) - if the projectname,services co. name, month and year matches.   I have thought about concatenating the four criteria into one long wordstring in a new column to compare, but now sure how to use this to subtract two other columns if the workstrings match?    Any help would be greatly appreciated!  Thank you, 

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    Hi Ellen,

     

    For that sample

    formulas in column E could be

    =IFERROR(INDEX(Table1,MATCH(1,INDEX((A7=Table1[One])*(B7=Table1[Two])*(C7=Table1[Three])*(D7=Table1[Four]),0,1),0),5),"")

    Please see attached.

Resources