Index-Match based on 3 criteria

[Johnelbek]

Dear all,

I am currently using the following Index-Match formula to return values based on one criteria:

=IFERROR(INDEX($A$1:$M$2000;SMALL(IF($E$1:$E$2000=$D$5;ROW($E$1:$E$2000);"");ROW($A1));MATCH(K$103;crystalheader;0));"")

The condition check is: "IF($E$1:$E$2000=$D$5;" where $D$5 is the name of a city. I wish to include to more criteria, to further narrow the values which are returned. I need to do a check on additionally two values which are located in $E$5 & $F$5.

So basically, the user selects 3 values from a dropdown and the index-match will return the corresponding values. I am having difficultes including the 2 other critieria. I've tried with an AND function:

SMALL(IF((AND($E$1:$E$2000=$D$5);($F$1:$F$2000=$E$5);($G$1:$G$2000=$E$5));ROW($E$1:$E$2000);""). This however, does not work.

Any ideas would be highly appreciated.
Thanks.

 

[Johnelbek]

Solved with:

IF(($E$1:$E$2000=$D$5)*($F$1:$F$2000=$E$5)*($G$1:$G$2000=$E$5))

 

[Aladin Akyurek]

Dear all,

I am currently using the following Index-Match formula to return values based on one criteria:

=IFERROR(INDEX($A$1:$M$2000;SMALL(IF($E$1:$E$2000=$D$5;ROW($E$1:$E$2000);"");ROW($A1));MATCH(K$103;crystalheader;0));"")

The condition check is: "IF($E$1:$E$2000=$D$5;" where $D$5 is the name of a city. I wish to include to more criteria, to further narrow the values which are returned. I need to do a check on additionally two values which are located in $E$5 & $F$5.

So basically, the user selects 3 values from a dropdown and the index-match will return the corresponding values. I am having difficultes including the 2 other critieria. I've tried with an AND function:

SMALL(IF((AND($E$1:$E$2000=$D$5);($F$1:$F$2000=$E$5);($G$1:$G$2000=$E$5));ROW($E$1:$E$2000);""). This however, does not work.

Any ideas would be highly appreciated.
Thanks.

Solved with:

IF(($E$1:$E$2000=$D$5)*($F$1:$F$2000=$E$5)*($G$1:$G$2000=$E$5))

What is the first cell you enter the formula?