Pull data from multiple sheets that match varying criteria

[T1991]

In excel, I am using this formula:

=IFERROR(INDEX(Master!$G:$G,AGGREGATE(15,6,ROW(Master!$G$3:$G$98)/(Master!$H$3:$H$98=$A$13)/(Master!$I$3:$I$98=$B$6)/(Master!$J$3:$J$98=$A$4)/(ISNA(MATCH(Master!$G$3:$G$98,B$12:B12,0))),1)),"")

The formula checks Master sheet and returns a value from column G that meets the criteria. However, I want to add additional sheets to this check. They are:

Sheet name: Support 1
Return value column: C
Criteria:
$B$3:$B$98=$A$4
$D$3:$D$98=$A$13
$E$3:$E$98=$B$6

Sheet name: Support 2
Return value column: D
Criteria:
$B$3:$B$98=$A$4
$G$3:$G$98=$A$13
$E$3:$E$98=$B$6

The intention is that it checks all three sheets for any matches and returns any singular instances of values that match. How would I add this to the formula?

 

[bobaol]

Hi T1991, assume your current formula is in column AA, how about write a formula for Support1 in column AB, a formula for Support2 in column AC, then a formula in AD to combine the three sheets? It gets a lot simpler writing simple formulas in different columns, then combining them in an aggregate column. You have a whole bunch of space to work in, so consider simple formulas in different columns to answer your question, then once your have an answer, ask the next question in a different column and so forth. it is a lot easier than a huge formula in one cell. hope this helps. cheers!