Using an Excel sheet while preparing some presentations, or accounts can be a daunting task without the knowledge of its functions. There are various functions in Microsoft Excel that you can use to create shortcuts and ease your work.
Let’s see some of the Microsoft Excel and how you can use them.
IF Function
Excel’s most popular function is the IF function, it helps to make a logical comparison between values and what you expect from them. It always has two results option, either true or false. You can use the IF function to test a condition, if the condition is a true return to one value and if the condition is a false return to another value.
Example: If the number in cell A2 is greater than 5, the formula returns “OK”; otherwise 0 is returned. Formula example for the same: =IF(A2>5, “OK”,)
AND Function
The AND function of excel is a logical function. If you wish to get more than one condition at the same time, you can use AND function. Inside the IF function, it can be used as the logical test if you want to avoid extra nests of IF functions. You can also combine AND function with the OR function.
Example: Test if a number in A5 is greater than zero and less than 15. =AND(A5>0, A5<15). The function here will test two results for A5.
OR Function
To test the multiple conditions at the same time you can use the OR function. Just like AND function OR function also returns either “true” or “false”. Inside the IF function, you can use the OR function to avoid extra Ifs. You can also combine OR with AND to get the results.
Example: To test A5 for either “x” or “y”. =OR(A5=”x”, A5=”y”). again just like AND the OR function will test two results for A5.
VLOOKUP Function
In VLOOKUP, “V” stands for vertical. If you have organized a table vertically in Excel and you need to find or retrieve data from it, you can use the VLOOKUP function of Excel. VLOOKUP supports exact matches and use (* ?) for partial matches. This function’s main purpose is to look up to a value in a table by matching the first column. In return, you get the matched value from the table. The syntax is VLOOKUP (value, table, col_index, [range_lookup]). Here:
- Value= the value to look for in the first column of a table.
- Table= the table from which data needs to be retrieved.
- Col_index= the column in the table from which to retrieve a value.
- Range_lookup= TRUE= approximate match, and False= exact match.
HLOOKUP Function
In HLOOKUP, “H” means horizontal. Just like VLOOKUP, HLOOKUP also helps in finding, and retrieving data, the only difference is that it performs a horizontal lookup by searching for a value in the top row and return the value in the same column based on the index_number. you can use the syntax, HLOOKUP(value,table,index_number,[approximate_match]), here:
- Value= the value to search for in the first row of the table.
- Table= two or more rows of data that is sorted in ascending order.
- Index_number= the row number in the table from which the matching value must be returned.
- Approximate_match= TRUE= approximate match, and False= exact match.
Using Excel functions is no rocket science, in just an attempt or two, you can easily start using it without any hassles. The functions are here to ease your problems and make your work smoother.