# Index-Match based on 3 criteria

#### Johnelbek

##### New Member
Dear all,

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

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.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Johnelbek

##### New Member
Solved with:

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

##### MrExcel MVP
Dear all,

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

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?

Replies
3
Views
70
Replies
8
Views
230
Replies
1
Views
124
Replies
3
Views
108
Replies
3
Views
180

1,195,990
Messages
6,012,721
Members
441,722
Latest member
tpaman1975

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back