Multiple Criteria and Multiple Values

[in_need_of_excel_help]

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!

 

[Eric W]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	Company Name	Period	Payment Deadline	Amount	Row	1/1/2020	12/31/2020		Company Name	Period	Payment Deadline	Amount
2	Company B	12/31/2018	10/1/2020	20	3				Company A	12/31/2020	10/1/2021	10
3	Company B	12/31/2019	9/11/2020	24	4				Company B	12/31/2020	10/1/2021	20
4	Company E	3/31/2020	4/1/2020	444	9				Company B	12/31/2018	10/1/2020	20
5									Company B	12/31/2019	9/11/2020	24
6									Company C	5/31/2020	3/1/2021	22
7									Company D	12/31/2014	10/1/2015	22
8									Company D	12/31/2015	10/1/2016	48
9									Company D	12/31/2013	10/1/2016	78
10									Company E	3/31/2020	4/1/2020	444
11
Sheet5

Cell Formulas
Range	Formula
A2:D6	A2	=IF($E2="","",INDEX(I$2:I$10,$E2))
E2:E6	E2	=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]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	Company Name	Period	Payment Deadline	Amount	Row	1/1/2020	12/31/2020		Company Name	Period	Payment Deadline	Amount
2	Company B	12/31/2018	10/1/2020	20	3				Company A	12/31/2020	10/1/2021	10
3	Company B	12/31/2019	9/11/2020	24	4				Company B	12/31/2020	10/1/2021	20
4	Company E	3/31/2020	4/1/2020	444	9				Company B	12/31/2018	10/1/2020	20
5									Company B	12/31/2019	9/11/2020	24
6									Company C	5/31/2020	3/1/2021	22
7									Company D	12/31/2014	10/1/2015	22
8									Company D	12/31/2015	10/1/2016	48
9									Company D	12/31/2013	10/1/2016	78
10									Company E	3/31/2020	4/1/2020	444
11
Sheet5

Cell Formulas
Range	Formula
A2:D6	A2	=IF($E2="","",INDEX(I$2:I$10,$E2))
E2:E6	E2	=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!