Forum Discussion
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 data and return the value based on 2 conditions:
1. Find the Project
2. Find the GL Code
If yes, enter value from spreadsheet. If no, enter $0.
I have uploaded a sample for illustration purposes. I am thinking I needed some combination of a lookup, nestedif and array formula. I would like the template to always lookto another tab for the data. My hope is to be able to export the data from bookeeping software to this tab and have the template pull the numbers.
Any help would be appreciated. Thank you.
Rob
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
4 Replies
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
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
Thank you very, very much!
It works :)
Rob
