Published on

Excel Basic Function Every User Should Know

Are you new to Excel or just a little rusty? Here are all the Functions you’ll need to know.

Here is a list of Excel Basic Functions:

Logical Functions

1. AND

and-function-in-excel

This Functions tests two or more condition to see if they are all true. It can be used to test that a series of numbers meet certain conditions. It can be used to test that a number or a date falls between an upper and lower limit. Normally the AND() function would be used in cunjuction with a function such as =IF()

Syntax

=AND(logical1,[logical2],...)
  • logical1: Required. The first condition that you want to test that can evaluate to either TRUE or FALSE
  • logical2: Optional. Additional condition that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.

Return value

When used by itself it will show TRUE or FALSE

Example

and-example-excel-basic-function1

The above example shows a list of examination results. Teacher wants to find the Students who scored above average in all three exams. The =AND() function has been used to test that each score is above the average. The result of TRUE is shown for Students who scored above average in all three exams.

Answer
=AND(C25>=AVERAGE($C$16:$C$25),D25>=AVERAGE($D$16:$D$25),E25>=AVERAGE($E$16:$E$25))

2. OR

or-function-in-excel

This function tests two or more conditions to see if any of them are TRUE. It can be used to test that at least one of a sereis of numbers meets certain conditions. Normally, OR() functions would be used in cunjuction with a function such as =IF().

Syntax

=OR(logical1, [logical2], ...)
  • logical1, logical2, ... Logical1 is required, subsequent logical values are optional. 1 to 255 conditions you want to test that can be either TRUE or FALSE.

Return Value

When used by it self it will return TRUE or FALSE.

Example

or-example-function-in-excel

The above table shows a list of orders taken by a company. A handling charge of 1% of Cost is made on all rders paid by Visa pr Master cards. The =OR() function has been used to determine whether the charge needs to be applied.

Answer
=IF(OR(D20="Visa",D20="Master"),C20*1%,0)

3. IF

if-function-in-excel

The IF function tests a condition. if the condition is met it is considered to be TRUE. if the condition is not met it is considered as FALSE. Depending upon the result. one of two actions will be carried out.

Syntax

=IF(logical_test, [value_if_true], [value_if_false])
  • logical_test : Required. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression.
  • value_if_true : Optional. The value that you want to be returned if the logical_test argument evaluates to TRUE.
  • value_if_false : Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE.

Note : Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests.

Return Value

The values you supply for TRUE or FALSE

Example

if-example-function-in-excel

This example uses the =AND() within the =IF() function. A builder merchant gives 10% discount on certain product lines. The discount is only given on product which are on Special Offer, when the Order Value is $2000 or above.

The =AND() function is used with the =IF() to check that the Product is on offer and that the value of the order is above $2000.

4. NOT

not-function-in-excel

The NOT() function is used to negate a logical expression. It is used to reverse the result of a logical expression.

Syntax

=NOT(logical)
  • logical : Required. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression.

Return Value

When used by itself it will show TRUE or FALSE

Example

not-example-function-in-excel

The above table was used by a library to track books borrowed. The date the book was Taken out is entered. The period of the Loan is entered. The =NOT() function is used to determine if the book has been returned within the loan period, by adding the loan period to the date the book was Taken out.

if the book was not returned within the loan period, the Overdue is shown, otherwise Ok is shown.

Answer
=IF(NOT(D17<=B17+C17),"Overdue","OK")

5. IFERROR

iferror-function-in-excel

The IFERROR() function returns a custom result when a formula generates an error, and standard result when no error is determined. IFERROR() is an elegant way to trap and manage errors without using more complicated nested IF statements.

Syntax

=IFERROR(value,value_if_error)
  • value : Required. The argument that is checked for an error.
  • value_if_error : Required. The value that you want to be returned if the value argument generates an error.

Return Value

The value you supply for value_if_error only when the value argument generates an error.

Lookup and Reference Function

1. ChOOSE

choose-function-in-excel

The CHOOSE function is used to select one of a series of values based on the value of a specified index.

Syntax

=CHOOSE(index_num,value1,value2,value3,...)
  • index_num : Required. The index value that is used to select a value from the list of values.
  • value1, value2, value3, ... : Required. The list of values that are selected based on the index_num argument.

Return Value

The value that corresponds to the index_num argument.

Example

choose-example-function-in-excel
  • The above table was used to calculate the medals for athletes laking part in a race.
  • The Time for each athlete is entered.
  • The =RANK() function to calculate the finishing position of each athlete.
  • The =CHOOSE() then allocates the correct medal.
  • The =IF() has been used to filter out any position above 3, as this would cause the error of #VALUE to apear, due to the fact the =CHOOSE() has only three items in it.

2. HLOOKUP

hlookup-function-in-excel

The HLOOKUP function finds and retrieve a value from data in a horizontal table. The "H" in HLOOKUP stands for horizontal, and lookup values must apear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and wildcards(* ?) for finding partial matches.

Syntax

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
  • lookup_value : Required. The value to be found in the table.
  • table_array : Required. The array that contains the table to be searched.
  • row_index_num : Required. The row index number that is used to select a row from the table.
  • range_lookup : Optional. A Boolean to indicate exact match or approximate match. Default is TRUE means approximate match.

Return Value

The value that corresponds to the lookup_value argument.

Example with Exact match

hlookup-example-function-in-excel

In the screen above, the goal is to lookup the correct level for a numeric rating 1-4. In cell D9, the HLOOKUP formula, copied down, is:

Answer
=HLOOKUP(D9,$G$18:$j$19,2,FALSE)

3. VLOOKUP

vlookup-function-in-excel

The VLOOKUP function finds and retrieve a value from data in a vertical table. The "V" in VLOOKUP stands for vertical, and lookup values must apear in the first column of the table, moving vertically down. VLOOKUP supports approximate and exact matching, and wildcards(* ?) for finding partial matches.

Syntax

=VLOOKUP(lookup_value,table_array,column_index_num,range_lookup)
  • lookup_value : Required. The value to be found in the table.
  • table_array : Required. The array that contains the table to be searched.
  • column_index_num : Required. The column index number that is used to select a column from the table.
  • range_lookup : Optional. A Boolean to indicate exact match or approximate match. Default is TRUE means approximate match.

Return Value

The value that corresponds to the lookup_value argument.

4. INDEX

index-function-in-excel

This function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns. It can be used with a single block of data. or non-continuous blocks.

Syntax

=INDEX(array,row_num,[column_num])
  • array : Required. The array that contains the data to be indexed.
  • row_num : Required. The row number that is used to select a row from the array.
  • column_num : Optional. The column number that is used to select a column from the array.

Return Value

The value that corresponds to the row_num and column_num arguments.

Example

index-example-function-in-excel
=INDEX(C19:E22,E24,E25)

5. MATCH

match-function-in-excel
  • This function looks for an item in list and shows its position.
  • It can be used with text and numbers.
  • It can look for an exact match or an approximate match.
  • It can look for a match with wildcards (* ?).
  • MATCH is combined with the INDEX function to get a value at a matched position.

Syntax

=MATCH(lookup_value,lookup_array,[match_type])
  • lookup_value : Required. The value to be found in the lookup_array.
  • lookup_array : Required. The array that contains the data to be searched.
  • match_type : Optional. The number -1, 0, 1. The match_type argument sepcifies how Excel matches lookup_value with values in lookup_array. The default value for this argument 1.

NOTE:- The match_type 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.

Return Value

The position of the lookup_value argument in the lookup_array argument.

Example with Exact match

match-example-function-in-excel
=MATCH(F24,C25:C33,0)

In the formula above, the MATCH function is used to find the position of the value F24 in the range C25:C33. The 0 argument is used to specify the exact match.

Example with approximate match

match-example-function-in-excel
=MATCH(F40,C41:C50,1)

In the formula above, the MATCH function is used to find the position of the value F40 in the range C41:C50. The 1 argument is used to specify the approximate match.

Example with INDEX

match-example-function-in-excel
=INDEX(B57:C66,MATCH(F56,C57:C66,-1),1)

The tables above used to by a bus comapny taking booking for bus tours. They need to allocate a bus with enough seats for the all the passengers. The list of bus has been entered in a list. Then enter the number of passengers and the BUS ID is found by using the MATCH with INDEX function.

Discuss on Twitter • View on GitHub