Using an if statement to display an array

  • Thread starter Thread starter Gun_Maddie
  • Start date Start date
G

Gun_Maddie

Is it possible to use an if statement to return an array? I have
named two arrays and would like to display either based on a value
from another sheet. Using a macro would not help me, just due to the
fact I was told not to use a macro by my boss for this spreadsheet.
Here is what I have....

Array 1:
Example 1
Bill
Art
John

Array 2:
Example 2
Mike
Bill
Gary

{if('sheet1'!$A$1<10,example1,example2)}

So far the only thing that is displayed is the heading of the array.
Is there a way to display the heading and the contents?

Thanks
 
Gun_Maddie said:
Is it possible to use an if statement to return an array? I have
named two arrays and would like to display either based on a value
from another sheet. Using a macro would not help me, just due to the
fact I was told not to use a macro by my boss for this spreadsheet.
Here is what I have....

Array 1:
Example 1
Bill
Art
John

Array 2:
Example 2
Mike
Bill
Gary

{if('sheet1'!$A$1<10,example1,example2)}

So far the only thing that is displayed is the heading of the array.
Is there a way to display the heading and the contents?

Thanks
Click to expand...

You need to enter the formula into ALL the cells that will contain data from
one or other of the arrays. In your example, with each array consisting of a
header and three other items, you need to select four (adjacent) cells, then
type the equation and array-enter it (CTRL+SHIFT+ENTER). You will see the
same equation in each of the four cells, but Excel will know that each cell
must return the appropriate item from whichever array is chosen by $A$1.
 
Since Example1 and Example2 are vertical you must select a vertical range of
cells, enter the formula and compete with shift+ctrl+enter as explained by
Anon.
If you want the output array to be horizontal use =IF(....,
TRANSPOSE(Example1),TRANSPOSE(Example2)

Bernard
 
Back
Top