another index match issue

[rservice0320]

now I am into building a dashboard & trying to get the dynamics to work, but once again, the index match doesn't want to return the results.  Help would once again be highly appreciated!  I have attached a file.

Thank you!!

[#2 · Accepted solution]

@rservice0320 Here is my preferred formula but in the file I gave you 4 different example for those 4 different locations:

=FILTER(data[Amt],(E7=data[BU])*(F7=data[DATA TYPE])*($G$2=data[month])*($H$2=data[year]),"NA")

This is my preferred because it handles errors the best and it is using the structured references of the table. 

[#3]

@mtarler BTW, that does return an array of answers if there is more than 1 possible result.  You can force it to return the first by:

=INDEX(FILTER(data[Amt],(E7=data[BU])*(F7=data[DATA TYPE])*($G$2=data[month])*($H$2=data[year]),"NA"),1)

And if you don't have Office 365 and hence don't have FILTER, that file has other options using INDEX or SUMPRODUCT.  In those cases if there is more than 1 possible result the INDEX returns the last one and the SUMPRODUCT returns a sum of them.  Note that the FILTER version above could easily return SUM() or MAX() or MIN() .... instead of INDEX(.... , 1).

[#4]

thank you so much. I really appreciate it. my table is huge & wanted to make sure if it expands, it incorporates the index/match. I do have 365, so will give filter a go. thank you!