Excel Fundamentals

Home »  Excel Fundamentals

[accordion title=”Vlookup” is_open=”no”]

The Excel Vlookup function is a lookup and reference function. It searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

Vlookup stands for Vertical Lookup. Use VLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

Vlookup requires 4 items of information (arguments) in order to work.

=vlookup(lookup_value,table_array,col_index_num,[range_lookup])

 Lookup value The value to search for in the first column of table array Table array The list of data to search for the value in. The values in the first column of table_array are the values searched by lookup_value. Col index num The column number from the left of the data to be returned. A col index num of 1 returns the value in the first column in table_array; a col index num of 2 returns the value in the second column in table_array, and so on. Range lookup Logical value that could be true or false.This is an optional argument that if untouched is set to true. This requires the data list to be sorted by the leftmost column in ascending order. If the value is not found in the column then the closest match is returned.If set to false, the data list can remain unsorted and an exact match is returned. If the value is not found a #N/A error message is returned.

Let’s run a Vlookup on the stock sheet below in cell H7. When the code is entered in cell H4 the stock level will automatically appear in cell H7 as a result of the Vlookup function.

[/accordion]

[accordion title=”SUMIF” is_open=”no”]

The SUMIF function totals the values of a range that meet specific criteria. For example it will total only the orders from a specific company or after a specific date.

The syntax for SUMIF is:

=SUMIF(range, criteria, [sum_range])

 Argument Purpose Range The range of cells to evaluate. Criteria The condition that defines which cells are to be added. Sum range The actual cells to sum. If omitted the values in range are added.

[/accordion]

[accordion title=”INDEX & MATCH” is_open=”no”]

The Excel Index function is a lookup and reference function. The INDEX function can return an item from a specific position in a list, selected by the row and column number indexes.

The syntax for INDEX is:

= INDEX(array,row_num,column_num)

 Argument Purpose Array Is the range of cells or list of data from which value is to be found out Row Num Row number in which required value is placed in the array Column Num Column number in which required value is placed in the array.

MATCH function in excel returns the relative position of an item in an array that matches a specified value in a specified order.

The syntax for MATCH is:

=MATCH(lookup_value,lookup_array,match_type)

 Argument Purpose Lookup Value Value you want to match in lookup_array. Lookup Array The list of data to search for the value in. Match Type It is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.    If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value.If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value.If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value.If match_type is omitted, it is assumed to be 1.

The INDEX and MATCH functions are used together to perform lookups. The INDEX function grabs a cell from an array, and the MATCH function contributes the row and/or column position.

Example:

Following argument can be used if you want to find out the grade of student Frank using the Index and Match function:

=INDEX(\$A\$2:\$C\$6,MATCH(B8,A2:A6,0),MATCH(A9,A1:C1,0))

[/accordion]

[accordion title=”SUMPRODUCT” is_open=”no”]

The Microsoft Excel SUMPRODUCT function multiplies the corresponding items in the arrays and returns the sum of the results

SUMPRODUCT( array1, [array2, … array_n] )

array1, array2, … array_n are the ranges of cells or arrays that you wish to multiply. All arrays must have the same number of rows and columns. You must enter at least 2 arrays and you can have up to 30 arrays.

=SUMPRODUCT({1,2,3,4,5},{5,4,3,2,1})

The above example would return 35. The SUMPRODUCT calculates these arrays as follows:

=(1*5) + (2*4) + (3*3) + (4*2) + (5*1)

You could also reference ranges in Excel.

Based on the Excel spreadsheet above, you could enter the following formula:

=SUMPRODUCT(A1:E1,A2:E2)

[/accordion]

[accordion title=”COUNTA” is_open=”no”]

The COUNT() function will only count the cells that contain numbers in them. Fortunately the other variations cater for anything else you may wish to count.

An important one to know is COUNTA(). This function counts all the cells that are not blank, or empty. This means it counts cells containing numbers, text, formulas and even error messages as long as it has something in it.

It is written in the same way that the COUNT() is. For example the function below counts all the non blank cells in the range A2:A50.

=COUNTA(A2:A50)

[/accordion]

[accordion title=”Calculate Loan Payments” is_open=”no”]

The PMT() function in Excel is used to calculate the payments on a loan. To calculate loan payments enter;

=PMT(rate, nper, pv, [fv], [type])

Rate – The interest rate per period on the loan. For example, 5%/12 f or monthly payments on 5% APR.

Nper –.Number of periods or payments on the loan.

Pv – The present value. The total amount the payments are worth now.

Fv – The future value. The balance after the last payment is made. If left empty the fv is set to 0.

Type – When are the payments made? Enter 1 if payments are made at the beginning of the period, and 0 if payments are made at the end of the period. If left empty then 0 is used.

[/accordion]

[accordion title=”Excel 2010 Shortcuts” is_open=”no”]

Downlaod Excel 2010 Shortcuts PDF

[/accordion]