Index

[trone77]

Looking for a formula that can extract from columns B, C and D every time the value 3 appears in column B & 4 appears in column C.


The previous formula used was: {=IFERROR(INDEX(B$1:B$21,SMALL(IF($B$1:$B$21=$N$3,ROW($B$1:$B$21)),ROW(1:1))),"")}

Which only dealt with one criteria, and in this instance would have only returned every 3 digit string that had the value of 3 in the C column, however I am looking implement a second criteria in to the formula like the example below.

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	1	2	4										3	4	_
4	3	4	7
5	6	5	7									1	3	4	7
6	2	7	3									2	3	4	0
7	1	1	8									3	3	4	1
8	0	0	2									4
9	7	0	3									5
10	5	5	5									6
11	2	5	9
12	4	3	0
13	7	7	2
14	4	2	4
15	2	2	2
16	1	0	3
17	7	1	3
18	3	4	0
19	7	2	4
20	7	8	3
21	3	4	1

<tbody>
</tbody>

 

[Fluff]

How about
=INDEX($D$3:$D$21,AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/(($B$3:$B$21=$N$3)*($C$3:$C$21=$O$3)),ROWS($A$1:$A1)))

 

[Marcelo Branco]

Maybe something like this

	M​	N​	O​	P​	Q​	R​
1​
2​
3​		3​	4​	_​
4​						Row​
5​	1​	3​	4​	7​		2​
6​	2​	3​	4​	0​		16​
7​	3​	3​	4​	1​		19​
8​	4​
9​	5​
10​	6​

Formula in R5 (helper column)
=IFERROR(AGGREGATE(15,6,(ROW(B$3:B$21)-ROW(B$3)+1)/((B$3:B$21=N$3)*(C$3:C$21=O$3)),M5),"")

Formula in N5 copied across and down
=IF(ISNUMBER($R5),INDEX(B$3:B$21,$R5),"")

M.

 

[Aryatect]

Another version, here the output will be Column B + Column C:
{=IFERROR(INDEX(B$1:B$21+$C$1:$C$22,SMALL(IF(($B$1:$B$21=$N$3)*($C$1:$C$21=$O$3),ROW($B$1:$B$21)),ROWS(N$5:N5))),"")}

 

[trone77]

How about
=INDEX($D$3:$D$21,AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/(($B$3:$B$21=$N$3)*($C$3:$C$21=$O$3)),ROWS($A$1:$A1)))

First off thanks for the help, however when I tested your formula it returned a values of 7 in N5, O5, and P5, when it should have returned the values of 3 4 7 in N5, O5 and P5.

 

[Fluff]

That formula is only to retrieve the values from col D.
I don't see the point of using a formula to return the values you are looking for.

However if that's what you want then in N5 put
=INDEX(B$3:B$21,AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/(($B$3:$B$21=$N$3)*($C$3:$C$21=$O$3)),ROWS($A$1:$A1)))
and copy across & down

 

[Aryatect]

Hi, Try this:

Book1
	M	N	O	P
5	1	3	4	7
6	2	3	4	0
7	3	3	4	1
8	4
9	5
10	6
Sheet6

Cell Formulas
Range	Formula
N5	{=IFERROR(INDEX(B$1:B$21,SMALL(IF(($B$1:$B$21=$N$3)*($C$1:$C$21=$O$3),ROW($B$1:$B$21)),ROWS(N$5:N5))),"")}
O5	{=IFERROR(INDEX(C$1:C$21,SMALL(IF(($B$1:$B$21=$N$3)*($C$1:$C$21=$O$3),ROW($B$1:$B$21)),ROWS(N$5:N5))),"")}
P5	{=IFERROR(INDEX(D$1:D$21,SMALL(IF(($B$1:$B$21=$N$3)*($C$1:$C$21=$O$3),ROW($B$1:$B$21)),ROWS(N$5:N5))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[trone77]

Maybe something like this

	M​	N​	O​	P​	Q​	R​
1​
2​
3​		3​	4​	_​
4​						Row​
5​	1​	3​	4​	7​		2​
6​	2​	3​	4​	0​		16​
7​	3​	3​	4​	1​		19​
8​	4​
9​	5​
10​	6​

<tbody>
</tbody>

Formula in R5 (helper column)
=IFERROR(AGGREGATE(15,6,(ROW(B$3:B$21)-ROW(B$3)+1)/((B$3:B$21=N$3)*(C$3:C$21=O$3)),M5),"")

Formula in N5 copied across and down
=IF(ISNUMBER($R5),INDEX(B$3:B$21,$R5),"")

M.

Thanks so much Marcelo your formula worked.

 

[trone77]

Hi, Try this:

	M	N	O	P
5	1	3	4	7
6	2	3	4	0
7	3	3	4	1
8	4
9	5
10	6

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

Sheet6​

Array Formulas Cell Formula N5 {=IFERROR(INDEX(B$1:B$21,SMALL(IF(($B$1:$B$21=$N$3)*($C$1:$C$21=$O$3),ROW($B$1:$B$21)),ROWS(N$5:N5))),"")} O5 {=IFERROR(INDEX(C$1:C$21,SMALL(IF(($B$1:$B$21=$N$3)*($C$1:$C$21=$O$3),ROW($B$1:$B$21)),ROWS(N$5:N5))),"")} P5 {=IFERROR(INDEX(D$1:D$21,SMALL(IF(($B$1:$B$21=$N$3)*($C$1:$C$21=$O$3),ROW($B$1:$B$21)),ROWS(N$5:N5))),"")} <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>

That worked perfectly, thanks so much.

 

[trone77]

That formula is only to retrieve the values from col D.
I don't see the point of using a formula to return the values you are looking for.

However if that's what you want then in N5 put
=INDEX(B$3:B$21,AGGREGATE(15,6,(ROW($D$3:$D$21)-ROW($D$3)+1)/(($B$3:$B$21=$N$3)*($C$3:$C$21=$O$3)),ROWS($A$1:$A1)))
and copy across & down

That formula also worked thanks alot.