Array formulas help needed

[Guest]

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;sheet2!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

 

[Guest]

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

ctrl+shift+enter, not just enter

 

[Guest]

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

 

[T. Valko]

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=Sheet1!C3)*(Sheet2!D150=Sheet1!D3),0))