Can a Control Arrays return Text Values?

[Annelies]

Hi,

Just wondering whether it is possible for a Control (Formula) array in Excel to return a text value? We can't seem to get it to work.

Our formula looks like:

{=(IF('Raw Data'!$D$2:$D$1000=$B5,
IF('Raw Data'!$E$2:$E$1000=T1,
'Raw Data'!$F$2:$F$1000,"Could not return value")))}

In plain english, if D2:d1000=B5 AND E2:E1000=T1 Then return the (text) value in F2:F1000, otherwise return an error message.

We can get this to work if we change the returning range to a column that contains dates, and pop a Max() function around the entire thing.

So I can see no reason why it would default to the error message all the time, despite there being text in the range F2:f1000 where the other two conditions are true.

Any help that anyone can offer would be much appreciated.

Thanks

Annelies

 

[Annelies]

Hi,

Since posting this I have found a website which explains how to do this.

If anyone else wants to know, check out:

http://www.dailydoseofexcel.com/archives/2005/06/05/multi-column-match/