Multiple Criteria and Multiple Values

Joined
Feb 12, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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:

data.png


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!
 

Some videos you may like

Excel Facts

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,754
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
1Company NamePeriodPayment DeadlineAmountRow1/1/202012/31/2020Company NamePeriodPayment DeadlineAmount
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.
 
Solution
Joined
Feb 12, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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
1Company NamePeriodPayment DeadlineAmountRow1/1/202012/31/2020Company NamePeriodPayment DeadlineAmount
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!
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top