Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maybe an array is needed? | Excel Worksheet Functions | |||
Array formula expertise needed | Excel Worksheet Functions | |||
Array formula needed | Excel Worksheet Functions | |||
Array formula needed | Excel Worksheet Functions | |||
Help needed on formulas | New Users to Excel |