Return Multiple Values with Multiple Conditions

[tryingtoexcelatexcel]

Hi All Experts,

=IFERROR(INDEX(ClientInfo[Oct],SMALL(IF('Agent Targets'!$C$3=ClientInfo[Sales PIC],ROW(ClientInfo[Oct])-ROW($B$3)+1),ROW(1:1))),"")

I am using this formula to return multiple values from a data base.

I would like to add another condition to this formula and have tried:

=IFERROR(INDEX(Calc[Client/Date/Destination/Pax],SMALL(IF(AND($C$3=Calc[Sales PIC Name],C$4=Calc[Sales Stage]),ROW(Calc[Client/Date/Destination/Pax])-ROW(CalcQ!$BD$3)+1),ROW(5:5))),"")



The first formula index's client info, and uses small to return the rows that I want to return IF C3 (sales name i.e. cynthia = array of sales names is true) and then use ctrl shift enter for the array formula


This formula works fine

But when I try to add another condition, i.e. IF ( AND ( sales name = sales name array, sales stage = sales stage array ) the formula stops working.

I am trying to add multiple criteria so I can do more than just return info for sales person.

Any help would be very much appreciated.

[AliGW]

Does this help?

=IFERROR(INDEX(index_array,SMALL(IF((lookup_array_1=lookup_value_1)*(lookup_array_2=lookup_value_2),ROW(index_array)),ROW(1:1))),"")

[tryingtoexcelatexcel]

ah yes * works perfectly thank you very much.

[AliGW]

You're welcome !