Index/Match and Array help

[wbm4x5220]

I'm trying to use Index/Match to produce an array that will show which values in ColA match a specific case in ColB. Below is a simple table. I'm looking to get an array with the values in ColA that match TRUE in ColB (3; 6; 7)

The formula I've been using only returns an array with the value of the first TRUE (3; 3; 3 etc.).

{=INDEX(A1:B10,MATCH(TRUE,A1:B10,0),1)}

Any help tweaking this would be appreciated.

1
2
3	TRUE
4	FALSE
5
6	TRUE
7	TRUE
8	FALSE
9
10

<tbody>
</tbody>

 

[steve the fish]

Like this?

=IF(B1:B10=TRUE,A1:A10)

Your formula cant produce anything far as I can tell. The match part is invalid.

 

[63falcondude]

I think you're looking for something like this

How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP

Excel 2010
	A	B	C	D	E
1		1		3	TRUE
2		2		6
3	TRUE	3		7
4	FALSE	4
5		5
6	TRUE	6
7	TRUE	7
8	FALSE	8
9		9
10		10
Sheet1

Cell Formulas
Range	Formula
D1	{=IF(ISERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$E$1,ROW($A$1:$A$10)),ROW(1:1)),2)),"",INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10=$E$1,ROW($A$1:$A$10)),ROW(1:1)),2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[wbm4x5220]

63falcondude, thanks very much. Works like a charm!

 

[63falcondude]

63falcondude, thanks very much. Works like a charm!

Glad to help!

 

[Aladin Akyurek]

Try to add a few rows in front of the current row 1, do we still get correct answers?

 

[wbm4x5220]

Try to add a few rows in front of the current row 1, do we still get correct answers?

When I did that, it matched on the value in the cell down however many rows were inserted. So I added -x to the end of the array formula, where x = the number of inserted rows. That brought the formula result back to the correct start point.

 

[Aladin Akyurek]

When I did that, it matched on the value in the cell down however many rows were inserted. So I added -x to the end of the array formula, where x = the number of inserted rows. That brought the formula result back to the correct start point.

That's not the way we should cope with such eventuality.

Rather: In D1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$E$1,ROW($B$1:$B$10)-ROW($B$1)+1),ROWS($D$1:D1))),"")

 

[wbm4x5220]

That's not the way we should cope with such eventuality.

Rather: In D1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$E$1,ROW($B$1:$B$10)-ROW($B$1)+1),ROWS($D$1:D1))),"")

Thanks, I'll give it a try.