Forum Discussion
Ellen Class
Jun 18, 2018Copper Contributor
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
- SergeiBaklanDiamond 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.