I am trying to create a summary page that pulls data from different sheets that represent each year. It pulls the data based from cell S2 which is the fiscal year and uses indirect to pull from the corresponding tab name. It then looks up the sales location (B43) and matches with the title name of the corresponding sales total (S3). The formula works well, but for many of the locations, I have multiple rows of data. This formula only pulls the first row of data. A vlookup formula would not work for me because of the way the data sheets are sorted.
I have attempted to use sumproduct, sum, aggregate, n(If, sumifs to no avail. I just need this formula to pull all 2-10 rows instead of only the first row. My formula seems like it needs just a bit more. Is this possible?
=IFERROR(INDEX(INDIRECT("'"&$S$2&"'!$A$1:$AO$178"),MATCH($B43,INDIRECT("'"&$S$2&"'!$D$1:$D$178"),0),MATCH(S$3,INDIRECT("'"&$S$2&"'!$A$2:$AO$2"),0)),0)
I have attempted to use sumproduct, sum, aggregate, n(If, sumifs to no avail. I just need this formula to pull all 2-10 rows instead of only the first row. My formula seems like it needs just a bit more. Is this possible?
=IFERROR(INDEX(INDIRECT("'"&$S$2&"'!$A$1:$AO$178"),MATCH($B43,INDIRECT("'"&$S$2&"'!$D$1:$D$178"),0),MATCH(S$3,INDIRECT("'"&$S$2&"'!$A$2:$AO$2"),0)),0)
