Index match?

amyylouise · Apr 4, 2018

I think I need an INDEX MATCH formula here but I just can't figure it out.

Sheet 1

	Payee (B)	Amount (C)	Date (D)
1	Payee 1	130.08	April 20, 2018
2	Payee 2	40.00	April 21, 2018
3	Payee 3	131.25	April 6, 2018
4	Payee 4	145.59	April 29, 2018

-----
Sheet 2

	Date (A)	Payee (B)	Amount (C)
1
2
3

	Start (E)	End (F)
1	20-Apr	03-May

-----

OK, so if the date in column D of Sheet1 is between Sheet2 E1 and F1, I want that date entered into Sheet2 A1 and have the subsequent Payee and Amount details pulled as well.
Basically, I want the table A1:C3 to summarize all the bills due between the date range in E1:F1.

I hope this makes sense.

TIA!

Scott T · Apr 4, 2018

Excel 2010

A

B

C

1

Payee (B)

Amount (C)

Date (D)

2

Payee 1

130.08

4/20/2018

3

Payee 2

40

4/21/2018

4

Payee 3

131.25

4/6/2018

5

Payee 4

145.59

4/29/2018

Sheet1

Excel 2010

A

B

C

D

E

F

1

Date

payee

amount

4/20/2018

5/3/2018

2

3

4

5

6

7

8

9

10

11

Sheet2

Cell Formulas
Range	Formula
A2	{=IF(ROWS(A$2:A2)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A2))))}
A3	{=IF(ROWS(A$2:A3)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A3))))}
A4	{=IF(ROWS(A$2:A4)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A4))))}
A5	{=IF(ROWS(A$2:A5)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A5))))}
A6	{=IF(ROWS(A$2:A6)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A6))))}
A7	{=IF(ROWS(A$2:A7)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A7))))}
A8	{=IF(ROWS(A$2:A8)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A8))))}
A9	{=IF(ROWS(A$2:A9)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A9))))}
A10	{=IF(ROWS(A$2:A10)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A10))))}
A11	{=IF(ROWS(A$2:A11)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A11))))}
B2	{=IF(ROWS(A$2:A2)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A2))))}
B3	{=IF(ROWS(A$2:A3)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A3))))}
B4	{=IF(ROWS(A$2:A4)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A4))))}
B5	{=IF(ROWS(A$2:A5)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A5))))}
B6	{=IF(ROWS(A$2:A6)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A6))))}
B7	{=IF(ROWS(A$2:A7)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A7))))}
B8	{=IF(ROWS(A$2:A8)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A8))))}
B9	{=IF(ROWS(A$2:A9)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A9))))}
B10	{=IF(ROWS(A$2:A10)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A10))))}
B11	{=IF(ROWS(A$2:A11)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A11))))}
C2	{=IF(ROWS(A$2:A2)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A2))))}
C3	{=IF(ROWS(A$2:A3)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A3))))}
C4	{=IF(ROWS(A$2:A4)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A4))))}
C5	{=IF(ROWS(A$2:A5)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A5))))}
C6	{=IF(ROWS(A$2:A6)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A6))))}
C7	{=IF(ROWS(A$2:A7)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A7))))}
C8	{=IF(ROWS(A$2:A8)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A8))))}
C9	{=IF(ROWS(A$2:A9)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A9))))}
C10	{=IF(ROWS(A$2:A10)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A10))))}
C11	{=IF(ROWS(A$2:A11)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A11))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

amyylouise · Apr 4, 2018

Scott T said:
Excel 2010

A B C
1 Payee (B) Amount (C) Date (D)
2 Payee 1 130.08 4/20/2018
3 Payee 2 40 4/21/2018
4 Payee 3 131.25 4/6/2018
5 Payee 4 145.59 4/29/2018

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Excel 2010

A B C D E F
1 Date payee amount 4/20/2018 5/3/2018
2 4/20/2018 Payee 1 130.08
3 4/21/2018 Payee 2 40.00
4 4/29/2018 Payee 4 145.59
5
6
7
8
9
10
11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Array Formulas

Cell Formula
A2 {=IF(ROWS(A$2:A2)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A2))))}
B2 {=IF(ROWS(A$2:A2)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A2))))}
C2 {=IF(ROWS(A$2:A2)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A2))))}
A3 {=IF(ROWS(A$2:A3)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A3))))}
B3 {=IF(ROWS(A$2:A3)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A3))))}
C3 {=IF(ROWS(A$2:A3)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A3))))}
A4 {=IF(ROWS(A$2:A4)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A4))))}
B4 {=IF(ROWS(A$2:A4)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A4))))}
C4 {=IF(ROWS(A$2:A4)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A4))))}
A5 {=IF(ROWS(A$2:A5)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A5))))}
B5 {=IF(ROWS(A$2:A5)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A5))))}
C5 {=IF(ROWS(A$2:A5)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A5))))}
A6 {=IF(ROWS(A$2:A6)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A6))))}
B6 {=IF(ROWS(A$2:A6)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A6))))}
C6 {=IF(ROWS(A$2:A6)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A6))))}
A7 {=IF(ROWS(A$2:A7)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A7))))}
B7 {=IF(ROWS(A$2:A7)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A7))))}
C7 {=IF(ROWS(A$2:A7)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A7))))}
A8 {=IF(ROWS(A$2:A8)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A8))))}
B8 {=IF(ROWS(A$2:A8)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A8))))}
C8 {=IF(ROWS(A$2:A8)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A8))))}
A9 {=IF(ROWS(A$2:A9)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A9))))}
B9 {=IF(ROWS(A$2:A9)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A9))))}
C9 {=IF(ROWS(A$2:A9)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A9))))}
A10 {=IF(ROWS(A$2:A10)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A10))))}
B10 {=IF(ROWS(A$2:A10)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A10))))}
C10 {=IF(ROWS(A$2:A10)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A10))))}
A11 {=IF(ROWS(A$2:A11)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!C$2:C$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A11))))}
B11 {=IF(ROWS(A$2:A11)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A11))))}
C11 {=IF(ROWS(A$2:A11)>COUNTIFS(Sheet1!$C$2:$C$5,">="&Sheet2!$E$1,Sheet1!$C$2:$C$5,"<="&Sheet2!$F$1),"",INDEX(Sheet1!B$2:B$5,SMALL(IF(Sheet1!$C$2:$C$5>=Sheet2!$E$1,IF(Sheet1!$C$2:$C$5<=Sheet2!$F$1,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1)),ROWS(A$2:A11))))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks!

Scott T · Apr 4, 2018

You are welcome.

Index match?

amyylouise

New Member

Excel Facts

Scott T

Well-known Member

amyylouise

New Member

Scott T

Well-known Member

Similar threads

Forum statistics

Share this page

Index match?

amyylouise

New Member

Excel Facts

Scott T

Well-known Member

amyylouise

New Member

Scott T

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock