# INDEX, SMALL Functions with Multiple Criteria

#### jworkman7

##### Board Regular
The below formula returns #Value. The problem is in the bolded portion of the formula. It works if I remove the AND and do only 1 criteria, but I can't make it work with two criteria

=INDEX(Sheet3!\$D\$3:\$D\$1031,(SMALL(IF(AND(Sheet3!\$S\$3:\$S\$1031="YES",Sheet3!\$Q\$3:\$Q\$1031=E46)),ROW(Sheet3!\$D\$3:\$D\$1031)-ROW(Sheet3!\$D\$3)+1),ROWS(Sheet3!\$D\$3:\$D3))))

I've also tried the below, but it also returns an error:

=INDEX(Sheet3!\$D\$3:\$D\$1031,(SMALL(IF((Sheet3!\$S\$3:\$S\$1031="YES")*(Sheet3!\$Q\$3:\$Q\$1031=E46)),ROW(Sheet3!\$D\$3:\$D\$1031)-ROW(Sheet3!\$D\$3)+1),ROWS(Sheet3!\$D\$3:\$D3))))

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
1st are you entering this using CTRL SHIFT ENTER, and not just enter?

2nd, this should work...
=INDEX(Sheet3!\$D:\$D,(SMALL(IF((Sheet3!\$S\$3:\$S\$1031="YES")*(Sheet3!\$Q\$3:\$Q\$1031=E46)),ROW(Sheet3!\$D\$3:\$D\$1031)),ROWS(Sheet3!\$D\$3:\$D3)))

1st are you entering this using CTRL SHIFT ENTER, and not just enter?

2nd, this should work...
=INDEX(Sheet3!\$D:\$D,(SMALL(IF((Sheet3!\$S\$3:\$S\$1031="YES")*(Sheet3!\$Q\$3:\$Q\$1031=E46)),ROW(Sheet3!\$D\$3:\$D\$1031)),ROWS(Sheet3!\$D\$3:\$D3)))

FD, your formula works, thank you! However,it didn't work at first. I discovered that I had errors in column Q,which was causing the#Value. I inserted an IFERROR and it works now.

Great stuff, happy to help

Replies
5
Views
560
Replies
0
Views
196
Replies
5
Views
298
Replies
2
Views
319
Replies
9
Views
340

1,203,069
Messages
6,053,351
Members
444,655
Latest member
didr

### 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