Array lookup

[Rolly_Sefu]

Hello.

I have the following array: {2;FALSE;5;FALSE;FALSE;6} - row numbers

Is it possible to get the values from the range A1:A6 based on the array above and get this result => {A2;FALSE;A5;FALSE;FALSE;A6} ?

I can not use SMALL function since I need the values from A1:A6 to remain in an array.

Help please.

 

[VBA Geek]

=if({2,false,5,false,false,6},index(a1:a6,n(if(1,{2,false,5,false,false,6}))))

 

[Rolly_Sefu]

works perfectly.

1 question if I evaluate the row_num from index I get {2;0;5;0;0;6}, how come index can get it right with the N function but without it will only return 2.

I tried to evaluate the index after I evaluated the row_num and it also result only the 2.

 

[VBA Geek]

the N wrapped around the IF coerces INDEX to return multiple results