# Multiple Criteria and Multiple Values

#### in_need_of_excel_help

##### New Member
Hi Guys,

Long time lurker here. Some of the responses to other users' problems have often helped me in the past, but unfortunately I've been defeated and I need some assistance!

Here is my spreadsheet:

The formula in column A is =IFERROR(INDEX(\$I\$2:\$I\$21,SMALL(IF((\$K\$2:\$K\$21>=\$F\$1)*(\$K\$2:\$K\$21<=\$G\$1),ROW(\$I\$2:\$I\$21)),ROW(1:1))-1,1),"")

It's pulling data in columns I - L with the criteria: if the payment deadline is between the range in Cell F1 and G1, then it will list all the relevant companies in column A. Thanks to you guys I found this formula and it's working great!

These are the formulas I have for columns B,C &D

B: =IFERROR(INDEX(\$J\$2:\$J\$21,MATCH(1,INDEX((\$I\$2:\$I\$21=A2)*(K\$2:\$K\$21>=\$F\$1)*(\$K\$2:\$K\$21<=\$G\$1),0),0)),"")
C: =IFERROR(INDEX(\$K\$2:\$K\$21,MATCH(1,INDEX((\$I\$2:\$I\$21=A2)*(K\$2:\$K\$21>=\$F\$1)*(\$K\$2:\$K\$21<=\$G\$1),0),0)),"")
D: =IFERROR(INDEX(\$L\$2:\$L\$21,MATCH(1,INDEX((\$I\$2:\$I\$21=A2)*(K\$2:\$K\$21>=\$F\$1)*(\$K\$2:\$K\$21<=\$G\$1),0),0)),"")

All I wanted to do here was just list the information from the other columns. I read that Vlookup wouldn't be possible here, so I used 'Index' and 'Match' instead. Sadly it doesn't work if one company has two different periods, which both satisfy the payment deadline (Like the example above, where company B has two results satisfying the criteria), it won't display the second value. It instead repeats the first value.

If there was only one company with one period, the formula works fine.

Any help correcting this will be greatly appreciated!

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Eric W

##### MrExcel MVP
Welcome from out of the shadows!

I'd recommend you create another column. I used E. Then take the part out of your original formula that locates the row, and put the row in that column. You can now hide that column if you want. Then The A2:D10 formulas are all the same, simple INDEX functions.

Book2
ABCDEFGHIJKL
2Company B12/31/201810/1/2020203Company A12/31/202010/1/202110
3Company B12/31/20199/11/2020244Company B12/31/202010/1/202120
4Company E3/31/20204/1/20204449Company B12/31/201810/1/202020
5     Company B12/31/20199/11/202024
6     Company C5/31/20203/1/202122
7Company D12/31/201410/1/201522
8Company D12/31/201510/1/201648
9Company D12/31/201310/1/201678
10Company E3/31/20204/1/2020444
11
Sheet5
Cell Formulas
RangeFormula
A2:D6A2=IF(\$E2="","",INDEX(I\$2:I\$10,\$E2))
E2:E6E2=IFERROR(AGGREGATE(15,6,(ROW(\$K\$2:\$K\$10)-ROW(\$K\$2)+1)/(\$K\$2:\$K\$21>=\$F\$1)/(\$K\$2:\$K\$21<=\$G\$1),ROWS(\$E\$2:\$E2)),"")

Also, please consider using the XL2BB tool (see my signature or the reply box). It's easy to install and use, and it makes it much easier for people to help you.

#### in_need_of_excel_help

##### New Member
Welcome from out of the shadows!

I'd recommend you create another column. I used E. Then take the part out of your original formula that locates the row, and put the row in that column. You can now hide that column if you want. Then The A2:D10 formulas are all the same, simple INDEX functions.

Book2
ABCDEFGHIJKL
2Company B12/31/201810/1/2020203Company A12/31/202010/1/202110
3Company B12/31/20199/11/2020244Company B12/31/202010/1/202120
4Company E3/31/20204/1/20204449Company B12/31/201810/1/202020
5     Company B12/31/20199/11/202024
6     Company C5/31/20203/1/202122
7Company D12/31/201410/1/201522
8Company D12/31/201510/1/201648
9Company D12/31/201310/1/201678
10Company E3/31/20204/1/2020444
11
Sheet5
Cell Formulas
RangeFormula
A2:D6A2=IF(\$E2="","",INDEX(I\$2:I\$10,\$E2))
E2:E6E2=IFERROR(AGGREGATE(15,6,(ROW(\$K\$2:\$K\$10)-ROW(\$K\$2)+1)/(\$K\$2:\$K\$21>=\$F\$1)/(\$K\$2:\$K\$21<=\$G\$1),ROWS(\$E\$2:\$E2)),"")

Also, please consider using the XL2BB tool (see my signature or the reply box). It's easy to install and use, and it makes it much easier for people to help you.
Thank you so much!

Replies
3
Views
127
Replies
0
Views
230
Replies
4
Views
49
Replies
7
Views
336
Replies
5
Views
165

1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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