Defining names in Excel involves giving a range of cells a unique name, which means that you can easily refer to a specific defined range, e.g. while creating a function. This is especially applicable in the following functions:
as the first argument
MATCH as the second argument
VLOOKUP and HLOOKUP as the second argument.
Formulas containing names instead of ranges are more understandable and easier to use, provided that the names we give will be legible. These names are also used in VBA programming.
In the Formulas card you will find the Defined names section:
How to define name for the range?
• Select the relevant range. • In the Name Box, enter the unique name of the selected range. Do not use any illegal special characters, e.g. spaces, $, @, #,! or arithmetic operators (+, -, /, *, ^), comparison (<> =) and the text operator (&). • Accept by pressing the Enter key.
Instead of the Name box you can click Formulas card then Define Name, set up: the name, scope, comment and refers to. In addition, the defined name cannot only refer to a cell or range of cells, but it also contains a formula or a specific value or text.
Create multiple names from the selection
• Select a range of data which contains multiple columns or rows with the labels at the beginning or the end. • Click Formulas card then Create form Selection or shortcuts[Ctr] + [Shift] + [F3]. • Select options based on what you want to name.
• Click Formulas card then Name Manager [Ctrl] + [F3]. • Create another new name, edit existing name, delete it, filter it and sort it
Insert the defined name into the formula
Simple click [F3] to obtain a list of created names and select the relevant one or start typing existing name and select the relevant one.
= IF ( Logical Test , Value if True , Value if False )
Logical test Examples:
• Equal to: A1=A2. • Higher than: A1>A2. • Higher or equal than: A1>=A2. • Less than: A1<A2. • Less or equal than: A1<=A2. • Not equal to: A1<>A2.
If we do not declare a third argument, because it is not mandatory, it will return FALSE by default. If we want to return the text as a result in the second or third argument, it must be enclosed in quotation marks.
If cell A1 value is higher then five, formula return Pass otherwise Fail.
In the case when we want to apply one logical condition, creating an IF function is simple and obvious. However, if we want to introduce further conditions, everything gets slightly more complicating. In this case, the function is nested, the next function (function in function or multiple) as an argument of the function.
Nesting of the IF function
I think each of us, at least once, has found it difficult to master the IF function when it is nested. That is, when the IF function was an argument of the IF function at least once. In addition, the nested function is a burden on the work of the system during conversion, of course, when we often use this function in the worksheet. I will now give you a simple example of an IF function nested once. In place of the third argument, a new IF function containing another condition is introduced.
= IF (( Logical Test , Value if True , IF ( Next Logical Test , Value if True , Value if False ))
If cell A1 value is higher then five, formula return Excellent, if not but higher than three return Good, otherwise Poor.
IFS function alternative
In 2016, an alternative to the IF function appeared in Excel, i.e. the IFS function, which gives the possibility to introduce more logical conditions than one, which simplifies the creation of the formula and significantly speeds up its work.
= IFS ( First logical Test , Value if True , [ Next Logical Test , Value if True ] , ...)
If cell A1 value is higher then five, formula return Excellent, if not but higher than three return three, otherwise return error #NA.
It can happen that more than once the condition is met in the arguments of the IFS function. In this case, the one that is recorded first is decisive.
In this case, if the content of cell A1 is higher than three, the function will always return Good (never return Excellent). Therefore, the order in which the conditions are saved is important. If none of the conditions are met, the formula will return error #NA.
Benefits of IFS over nested IF:
• The IFS function allows you to easily enter further logical conditions without nesting. • Due to the lack of nesting, the performance of IFS is much more efficient • Allows you to apply twice as many, or 127 conditions, while the nested formula 64 conditions only. • If none of the conditions are met, IFS will return error #NA. In this case, it is worth using IFEROOR. In this case, in the second argument we give what should be if an error occurs, if we need it.
= IFERROR ( IFS ( Logical Test , Value if True , Next Logical Test , Value if True ) , Value if False )
Forget about the VLOOKUP function. The new XLOOKUP function has arrived, being a genuine alternative for VLOOKUP, HLOOKUP, INDEX and MATCH. If you know how to use the XLOOKUP function, you will never bother to use VLOOKUP anymore. The new feature, which has been recently introduced, has more options and is characterised by its flexibility, efficiency and user-friendliness.