Forum Discussion

Josh Powers's avatar
Josh Powers
Copper Contributor
Jan 26, 2018

Have excel compute multiple responses w/ multiple criteria from different sheets on the same work

 

I am using drop down menu's that are linked using name manager to address 4 different criteria that will then pull information from other tables. Below is the formula that I am using right now. The first one I was using worked perfectly but would only compute the first response it saw that matched the criteria and would not display others that also fit the same criteria. I found a formula online that would display all the matches to the criteria and tried to mix the two formula to get the response I want, but now I am at an impass because excel is not recognizing what I am trying to do if anyone can provide any feedback.

 

FORMULA 1: =IF(AND($E$7="Monthly",$B$7="Fund_Managers"),INDEX('Raw Entries'!$B$4:$K$157,MATCH($B$9&$C$9,'Raw Entries'!$B$4:$B$157&'Raw Entries'!$C$4:$C$157,0),4),0)        *With this formula I also use (Ctrl...Shift...Enter) to index the information--- This is also the formula that works but only displays the first match not others as well

FORMULA 2: =IF(AND($E$7="Monthly",$B$7="Fund_Managers"),INDEX('Raw Entries'!$B$4:$B$157&'Raw Entries'!$C$4:$C$157,AGGREGATE(15,6,(ROW('Raw Entries'!$B$4:$B$157&'Raw Entries'!$C$4:$C$157)-ROW('Raw Entries'!$B$4&'Raw Entries'!$C$4)+1)/('Raw Entries'!$B$4:$B$157&'Raw Entries'!$C$4:$C$157=$B$9&$C$9),ROWS(E$9:E9)),4),0)

                                              *This is the formula that does not work and I tried to mix with the one I made above, and the one I found online

 

 

No RepliesBe the first to reply