Array formulas help needed
 

I love array formulas (though, Im not expert at them). My favorite is:
{=sum(if(sheet2!a1:a100=a3;if(sheet2!b1:b100=b3;sh eet2!c1:c100)))}
Which will find the appropriate number in sheet2!c1:c100.
To my great disappointment, Ive discovered today that it will display the
expected result if the range c1:c100 contains numeric data only and it wont
display it if theres text data.
So, I got stuck here. Is there a way to find cells containing text data the
same way?
Thanx,
ArthurN

Array formulas help needed
 

=INDEX(Sheet2!C1:C100,MATCH(1,(Sheet2!A1:A100=A3)* (Sheet2!B1:B100=B3),0))

ctrl+shift+enter, not just enter


"ArthurN" wrote:

I love array formulas (though, Im not expert at them). My favorite is:
{=sum(if(sheet2!a1:a100=a3;if(sheet2!b1:b100=b3;sh eet2!c1:c100)))}
Which will find the appropriate number in sheet2!c1:c100.
To my great disappointment, Ive discovered today that it will display the
expected result if the range c1:c100 contains numeric data only and it wont
display it if theres text data.
So, I got stuck here. Is there a way to find cells containing text data the
same way?
Thanx,
ArthurN

Array formulas help needed
 

Hi, and thank you,
I've seen this formular in inbuild addins the only trouble is that I don't
see how I can implement several conditions into it:
if sheet2!a1:a50=sheet1!a3
and sheet2!b1:b50=sheet1!b3
and sheet2!c1:c50=sheet1!c3
and sheet2!d1:d50=sheet1!d3
----then fetch the cell (with text data) in sheet2!f1:f50

Thank you again,
ArthurN

"Teethless mama" wrote:

=INDEX(Sheet2!C1:C100,MATCH(1,(Sheet2!A1:A100=A3)* (Sheet2!B1:B100=B3),0))

ctrl+shift+enter, not just enter


"ArthurN" wrote:

I love array formulas (though, Im not expert at them). My favorite is:
{=sum(if(sheet2!a1:a100=a3;if(sheet2!b1:b100=b3;sh eet2!c1:c100)))}
Which will find the appropriate number in sheet2!c1:c100.
To my great disappointment, Ive discovered today that it will display the
expected result if the range c1:c100 contains numeric data only and it wont
display it if theres text data.
So, I got stuck here. Is there a way to find cells containing text data the
same way?
Thanx,
ArthurN

Array formulas help needed
 

You just add the additional conditions in the same way:

=INDEX(Sheet2!F1:F50,MATCH(1,(Sheet2!A1:A50=Sheet1 !A3)*(Sheet2!B1:B50=Sheet1!B3)*(Sheet2!C1:C50=Shee t1!C3)*(Sheet2!D1:D50=Sheet1!D3),0))


--
Biff
Microsoft Excel MVP


"ArthurN" wrote in message
...
Hi, and thank you,
I've seen this formular in inbuild addins the only trouble is that I don't
see how I can implement several conditions into it:
if sheet2!a1:a50=sheet1!a3
and sheet2!b1:b50=sheet1!b3
and sheet2!c1:c50=sheet1!c3
and sheet2!d1:d50=sheet1!d3
----then fetch the cell (with text data) in sheet2!f1:f50

Thank you again,
ArthurN

"Teethless mama" wrote:

=INDEX(Sheet2!C1:C100,MATCH(1,(Sheet2!A1:A100=A3)* (Sheet2!B1:B100=B3),0))

ctrl+shift+enter, not just enter


"ArthurN" wrote:

I love array formulas (though, I'm not expert at them). My favorite is:
{=sum(if(sheet2!a1:a100=a3;if(sheet2!b1:b100=b3;sh eet2!c1:c100)))}
Which will find the appropriate number in sheet2!c1:c100.
To my great disappointment, I've discovered today that it will display
the
expected result if the range c1:c100 contains numeric data only and it
won't
display it if there's text data.
So, I got stuck here. Is there a way to find cells containing text data
the
same way?
Thanx,
ArthurN