Forum Discussion
Solved
Conditional if/find/lookup formula
Hi, I am trying to build a financial statement template that uses formulas to pull data from another worksheet. The difficulty I am having is finding a formula that will look to a whole sheet of...
Hi Rob,
Array (Ctrl+Shift+Enter) formula INDEX/MATCH could work, for revenue
=IFERROR(INDEX(Data!$D:$D, MATCH(1,(Data!$F:$F=Template!C$2)*(Data!$C:$C=Template!$B4),0),1),0)
and for expenses
=IFERROR(INDEX(Data!$E:$E, MATCH(1,(Data!$F:$F=Template!C$2)*(Data!$C:$C=Template!$B9),0),1),0)
see attached file
I like to use SUMIFS for this sort of work
=SUMIFS(Data!$D:$D,Data!$B:$B,$A4,Data!$F:$F,C$2)
+SUMIFS(Data!$E:$E,Data!$B:$B,$A4,Data!$F:$F,C$2)
Also note that this relies heavily on getting all the descriptions in the right spots so worth adding cross checks.
Cell F23 on the data sheet should probably have Project 3 typed in.
Thanks Wyn!
I appreciate you taking the time to solve this issue.
Rob
