Hi, I would like to use an array formula to retrive an array of data from a
column given a certain condition, for sake of example let the columns look
like this

A B
1 1
2 2
1 3
4 4
5 5

Selecting 2 cells and using the array formula (ctrl+shift+enter)
If("A1:A5"=1;"B1:B5";"") yields the value 1 in both cells.

What do I do to get the full array, in this case 1,3 in the cells
respectively?

I want to use this as a VLOOKUP function where the lookup range doesnt have
unique elements so any other suggestions on this matter are highly
appriciated as well.

Brgds/ Kalle

Enter the below formula and copy down as required..which should give 1,3 etc;.

=SMALL(IF($A$1:$A$5=1,$B$1:$B$5,""),ROW(A1))

If this post helps click Yes
---------------
Jacob Skaria


"Kalle" wrote:

Hi, I would like to use an array formula to retrive an array of data from a
column given a certain condition, for sake of example let the columns look
like this

A B
1 1
2 2
1 3
4 4
5 5

Selecting 2 cells and using the array formula (ctrl+shift+enter)
If("A1:A5"=1;"B1:B5";"") yields the value 1 in both cells.

What do I do to get the full array, in this case 1,3 in the cells
respectively?

I want to use this as a VLOOKUP function where the lookup range doesnt have
unique elements so any other suggestions on this matter are highly
appriciated as well.

Brgds/ Kalle

Correction...Try the below array formula..and copy down .

=INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5=1,ROW($B$1:$B$ 5)),ROW(A2)))

You can change the value 1 to a cell reference

=INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5=<cell reference,ROW($B$1:$B$5)),ROW(A2)))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Enter the below formula and copy down as required..which should give 1,3 etc;.

=SMALL(IF($A$1:$A$5=1,$B$1:$B$5,""),ROW(A1))

If this post helps click Yes
---------------
Jacob Skaria


"Kalle" wrote:

Hi, I would like to use an array formula to retrive an array of data from a
column given a certain condition, for sake of example let the columns look
like this

A B
1 1
2 2
1 3
4 4
5 5

Selecting 2 cells and using the array formula (ctrl+shift+enter)
If("A1:A5"=1;"B1:B5";"") yields the value 1 in both cells.

What do I do to get the full array, in this case 1,3 in the cells
respectively?

I want to use this as a VLOOKUP function where the lookup range doesnt have
unique elements so any other suggestions on this matter are highly
appriciated as well.

Brgds/ Kalle