Taking Array Arguments in IF function, what actually are going on?

[caniever]

Hi all, I am always confused as to what Excel is doing when putting array arguments into an IF function. for example, if i have the following 2 arrays:

Array A:
1, 2
3, 4
5, 6

Array B:
I, II, III

ref cell <--to input a number for comparison

and if I have the following formula:
=IF ( ref cell = Array A, Array B) <--Let's just assume the false situation is omitted here

If the ref cell is 5, theorectically the comparison will give the following True/False array:

False, False;
False, False;
True, False

So for the only True case above, does Excel just display the array "I, II, III" ?

I know for a fact that if the size of array B is "I, II", it will really display result in the corresponding position, if the comparison argument is True, i.e. if any of the 1, 3, 5 is true, the result will be I, if any of the 2, 4, 6 is true, the result will be II.....I am sorry if I am expressing myself in a confusing way....

Do the "dimentions" of Array A and Array B have to follow some kind of rules so that IF function can work normally?

 

[jtakw]

Hi,

I'm probably misunderstanding what you're asking, but you can't test a value to an array with the IF function, they have to be an Exact match to return the correct result.
The IF function will return TRUE if the First value within the array is an Exact match, otherwise will always return FALSE.
For what you're describing, you should look into the LOOKUP, VLOOKUP, HLOOKUP, or INDEX/MATCH functions

 

[caniever]

Hi all, I am always confused as to what Excel is doing when putting array arguments into an IF function. for example, if i have the following 2 arrays:

Array A:
1, 2
3, 4
5, 6

Array B:
I, II, III

ref cell <--to input a number for comparison

and if I have the following formula:
=IF ( ref cell = Array A, Array B) <--Let's just assume the false situation is omitted here

If the ref cell is 5, theorectically the comparison will give the following True/False array:

False, False;
False, False;
True, False

So for the only True case above, does Excel just display the array "I, II, III" ?

I know for a fact that if the size of array B is "I, II", it will really display result in the corresponding position, if the comparison argument is True, i.e. if any of the 1, 3, 5 is true, the result will be I, if any of the 2, 4, 6 is true, the result will be II.....I am sorry if I am expressing myself in a confusing way....

Do the "dimentions" of Array A and Array B have to follow some kind of rules so that IF function can work normally?

Sorry I wanted to say the formula is IF(Array A=ref cell, Array B) instead of the original above...

Thank jtakw for giving a diff perspective to the question.

 

[Marcelo Branco]

Do the "dimentions" of Array A and Array B have to follow some kind of rules so that IF function can work normally?

To avoid errors in comparisons they should ideally have the same dimensions. Or, at least, the second array must have the same number of columns or rows as the first.
What exactly are you trying to do?

M.

 

[jtakw]

Sorry I wanted to say the formula is IF(Array A=ref cell, Array B) instead of the original above...

Thank jtakw for giving a diff perspective to the question.

There's no difference, same answer as above:

Book1
	B	C	D
1	1	5	No
2	2	6
3	3	7
4	4	8
5	5	9
6
7	6
Sheet38

Cell Formulas
Range	Formula
D1	=IF(C1:C5=B7,B1:B5,"No")

 

[caniever]

Thank you for the response. I just want to understand the array argument mechanism of the IF function. So if it's like your said, both array A and B are of the same dimensions, e.g. 2 x 3, so there will be 6 comparisons to be made, right? (a set of 6 True/False outcomes). For instance, if the first TRUE appears in the third comparison, so at that moment, IF will automatically show the THIRD element of the array B, right? So that is some position reference "mechanism".

Just wanna know if the above mechanism is affected by the sizes of the arrays. I think it is rare for people to use IF function that way, but I am just curious.

 

[caniever]

There's no difference, same answer as above:

	B	C	D
No

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet38​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=IF(C1:C5=B7,B1:B5,"No")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Do you mean the value it shows is "No", but in essence it stores the array No, 2, No, No, No ?

 

[Aladin Akyurek]

Thank you for the response. I just want to understand the array argument mechanism of the IF function. So if it's like your said, both array A and B are of the same dimensions, e.g. 2 x 3, so there will be 6 comparisons to be made, right? (a set of 6 True/False outcomes). For instance, if the first TRUE appears in the third comparison, so at that moment, IF will automatically show the THIRD element of the array B, right? So that is some position reference "mechanism".

Just wanna know if the above mechanism is affected by the sizes of the arrays. I think it is rare for people to use IF function that way, but I am just curious.

=IF(A2:A4="JAD",B2:B6)

=IF(A2:A4=C2:C6,1,0)

The first is a filter action, the second a test for, in this equality, equality.

Select the formula of interest, go to the formula bar, hit F9 in order to see how each is evaluated. The parts which are missing elementwise will show up as #N/A evaluations.