Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use AND in ARRAY formula
I am using the below formula and it is working fine.
{=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$F$3,ROW($B $1:$B$10)),1))} Now I want to check two conditions in this array formula. for e.g. IF($A$1:$A$10=$F$3 and IF($C$1:$C$10=$E$3 Please Help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use AND in ARRAY formula
Hi,
Does this do what you want? =INDEX($B$1:$B$10,MATCH(1,($A$1:$A$10=$F$3)*($C$1: $C$10=$E$3),0)) Mike "Rakesh Gupta" wrote: I am using the below formula and it is working fine. {=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$F$3,ROW($B $1:$B$10)),1))} Now I want to check two conditions in this array formula. for e.g. IF($A$1:$A$10=$F$3 and IF($C$1:$C$10=$E$3 Please Help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use AND in ARRAY formula
Use cannot use logical functions (AND, OR, NOT) within an array formula
But AND can be replaced by multiplication and OR by addition Example IF(AND(A10, B11).... is the same as IF((A10)*( B11).... because you will get either 1 (TRUE) or 0 (FALSE) So I would expect this to work: {=INDEX($B$1:$B$10,SMALL(IF(($A$1:$A$10=$F$3)*(($C $1:$C$10=$E$3),ROW($B$1:$B$10)),1))}best wsihes--Bernard V LiengmeMicrosoft Excel MVPhttp://people.stfx.ca/bliengmeremove caps from email"Rakesh Gupta" wrote in ... I am using the below formula and it is working fine. {=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$F$3,ROW($B $1:$B$10)),1))} Now I want to check two conditions in this array formula. for e.g. IF($A$1:$A$10=$F$3 and IF($C$1:$C$10=$E$3 Please Help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use AND in ARRAY formula
Hi,
Here is another version =INDEX(B1:B10,MIN(IF((A1:A10=F3)*(C1:C10=E3),ROW(1 :10)))) If you are not copying the formula then you don't need the absolute cell references. Note also that SMALL(x,1) is the same as MIN at least in your example. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Rakesh Gupta" wrote: I am using the below formula and it is working fine. {=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$F$3,ROW($B $1:$B$10)),1))} Now I want to check two conditions in this array formula. for e.g. IF($A$1:$A$10=$F$3 and IF($C$1:$C$10=$E$3 Please Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |