Hi all,
I'm working on a formula in Excel for Mac for a report at work that will show when specific letters and reports are due for a specific grant funder and fiscal year. It is pulling this data from a table where the specific funders and fiscal years are on the right side and the data for what type of letter and when it is due are in a big table on the left.
I need the output to do all of the following:
1) Match for a specific funder as indicated in a cell in the main report
2) Match for a specific fiscal period as indicated in the cell below the funder cell
3) Finally it needs to find a specific text value within the table that includes a wildcard as the text value is a code for the type of report and includes a numerical value indicating the date when it is due
The formula I currently have written up looks like this:
=INDEX('Grants Calendar'!H3:AE51,SUMPRODUCT(('Grants Calendar'!B3:B60=C2)*('Grants Calendar'!F3:F60=C3)*('Grants Calendar'!H3:AE51="FINAL")*ROW('Grants Calendar'!H3:AE51)),1)
I am currently receiving the #N/A output even though I know the inputs in the funder and fiscal cells match up with an existing result. What can I do to write a formula that will produce the outcome I need? How can I tweak the existing formula so it will work?
Bookmarks