Today we are going to discuss about hidden capabilities of excel functions. If we get to know about these flexibilities, then we can knock the doors of advanced excel world. We can start doing the things which you thought impossible. Let's start diving,
If Function:
=IF(logical_test,[value_if_true],[value_if_false])
Logical_test argument in IF function, can accept comparative statement which will result in Boolean values.
We can even simply refer the Boolean values in logical_test argument.
This argument looks 1 as TRUE and 0 as FALSE, we all know that already. But,
This argument looks any number other than zero as TRUE and zero as FALSE.
Here, logical_test argument has one housed with curly braces. Value_if_False argument fed as range of size 4 rows and 2 columns, so we are forcing IF function to deliver the result as an array and so it returns "Megan" in 4 * 2 size array.
IFERROR function:
IFERROR function also works in the same concept as shown in the right-side pic,
Value argument in IFERROR function is fed with text "Megan" housed in curly braces,
Value_if_error argument fed with range of size
4 * 2 and so it throws the result in the same size.
INDEX function:
In above case, evaluation of INDEX formula shows only west, this is because we told INDEX to throw more than 1 result. But Index function is programmed to deliver one cell or the whole column or row (My version is 2021 and so it shows the result in cells, but in the excel version 2016 or less it will deliver only "West")
To overcome this, we can do as shown below,
Here, we wrapped the array inside IF and N function, to force the INDEX function to deliver multiple results.
Comments