Today we are going to discuss about excel formulas which has the ability to deliver the range reference as an output. There is no specific requirement here today, so we just try to test the capabilities of Excel functions. Lot to learn, let's dive in.
IF Function:
Here in F6 cell, IF formula is used in its simplest form.
=IF($C$2="Feb",D6,C6)
IF C2 cell value is 'Feb' then give me D6 cell else give me C6 cell. Since, C2 cell value is 'Jan', the formula returns C6 cell value 271 as output. What's in it to test the ability of the IF Function? till now nothing. But, let me ask one question,
What is the output of this function?
Is it the value from the cell or the cell reference itself?
To get some clarity, let me wrap this function in CELL function, before that let me show you the arguments of CELL function,
=CELL(info_type,[reference])
Info_type argument provides us the option to select the type as shown in the left side picture.
[reference] argument is optional, if we omit this argument, then this function will pick the active cell as the reference and will throw the result based on the selected type. If we refer any cell, then this function will throw the selected type of the referred cell. If we refer the range of cells, then this will throw the selected type of Top Left Cell in the range.
In our case, we can select 'address' as the type for info_type argument and use our IF function as the reference argument.
Then CELL function throws the range as the output. So, we understood that the output of IF function has the ability to act as the reference. We can use this ability to construct dynamic ranges as shown below.
We used range operator (Colon) after our IF construction and then referred D10 cell.
Here, IF function delivers the D6 as the reference, since we used colon and the range D10, it becomes D6:D10 and so it throws the values from D6:D10 after evaluation.
Now, we understood that IF function has the ability to deliver the output as reference.
We will try to find which are all the functions has the ability to deliver the reference as an output.
INDEX Function:
Formula in F6 cell,
=INDEX($C$6:$D$6,MATCH($C$2,$C$5:$D$5,0)):D10
The arguments of INDEX function are,
=INDEX(array,row_num,[column_num])
In the array argument, we referred C6:D6.
In the row_num argument, we used MATCH function to get the relative position.
Arguments of MATCH function are,
=MATCH(lookup_value,lookup_array,[match_type])
C2 cell is the lookup value, C5:D5 is the lookup array and match_type is zero, so Exact match. If C2 cell value is 'Jan', then match function will deliver 1 as output. If C2 cell value is 'Feb', then Match function will deliver 2 as output. From the array C6:D6, relative position of 2 is D6, so after evaluation INDEX function will deliver 311 as the output. To check its ability, we used range operator (Colon) after INDEX function and then referred D10 cell.
It refers to the range D6:D10 and so we are sure that INDEX function has the ability to deliver the range as output.
CHOOSE Function:
Now we are going to put CHOOSE function under scanner,
The arguments of this function,
=CHOOSE(index_num,value1,[value2],...)
Here, we used MATCH function in index_num argument. Earlier in this post, we discussed about MATCH function. This will deliver 1 or 2 based on month in C2 cell. If index_num is 1, then value1 argument is evaluated, if index_num is 2, then value2 argument is evaluated and so on.
In our case, CHOOSE function will deliver C6 as output. Then, we did the same workout as discussed earlier to check the output ability of this function and it passed this test.
XLOOKUP, INDIRECT, OFFSET, SWITCH functions have the ability to deliver the reference as output.
Classic VLOOKUP, HLOOKUP and LOOKUP functions delivers the cell value as output and so these functions are failed in this test.
Comments