Lookup and Reference, Finance and Character Functions

1.  CHOOSE:

The CHOOSE function returns a value corresponding to the given position from an array of numbers. The function is written as:

=CHOOSE (position, value1, [value2, … value n])

where the position is the position number. A value can be either a number, a cell reference, a formula/function, or a text value. If the value of the position argument is

less than one, the CHOOSE function returns #VALUE!

2.  VLOOKUP and HLOOKUP:

HLOOKUP is an abbreviation for Horizontal Lookup, and it may be used to obtain information from a database by scanning a row for matching values and then outputting from the relevant column. The video of these functions can also be watched in Excel tutorial videos for better understanding. While VLOOKUP looks for a value in a column, HLOOKUP looks for a value in a row. The functions are written as:

=HLOOKUP (value to look up, table array, row number)

=VLOOKUP (value to look up, table array, col index number)

3.  OFFSET:

The function OFFSET can be a bit complex for beginners and can be looked up in Excel training videos. It returns a range of cells. That is, it will return a specific number of rows and columns from a defined beginning range. The function is:

= OFFSET (reference cell/cells, rows, columns, [height], [width])

Reference argument here represents the cell range or the cell to offset. The rows argument represents the number of rows that should be offset below the beginning reference whereas the column argument represents the number of columns to offset to the right of the reference. The OFFSET function’s final two parameters are optional.

Finance Function

1.  ISPMT:

The ISPMT function calculates the interest paid on an investment over a specified time period. The way to write the function along with the arguments is:

=ISPMT (rate, period, number of periods, present value)

where the rate is the interest rate, the period is the time duration for which the interest is to be calculated, the third argument is the number of periods over which the loan is to be repaid and the last argument is the present value of the loan or investment.

2.  SLN:

The SLN work returns the straight-line deterioration of a resource. It is written as:

=SLN (salvage, cost, life)

where cost is the initial asset cost, the second argument salvage is the salvage life and life is the number of depreciation periods for the asset.

3.  FVSCHEDULE:

The future value of an investment with a variable interest rate is calculated using the FVSCHEDULE function. The function is:

=FVSCHEDULE (principal, schedule)

where the principal is the present valuation of the investment and schedule is the number of values of interest rates that are to be applied. If the empty range is specified, it means the interest rate is 0%.

Text Functions

1.  TRIM:

The TRIM function ensures that your functions do not produce errors as a result of disorderly spaces. It guarantees that no spaces are there in a cell. TRIM, in contrast to other functions that can work on a range of cells, exclusively operates on a single cell. It is written as:

=TRIM (text). For example,

=TRIM(A5): Removes any empty space in the value in cell A5.

2.  FIND:

The find function returns the position of a substring or even a character for that matter within a larger string. It is written as:

=FIND (text to find, text to find from, [start number])

where the first argument is the text, we want to find and the second argument is the string we want to traverse. The last argument is optional which specifies the index

in the string, the search should begin from.

3.  EXACT:

Exact is used to compare two strings and if they are the same, it returns TRUE, otherwise, it returns FALSE. The function is:

=EXACT (string1, string2, …)

where the arguments themselves are pretty self-explanatory. They can be either standalone words or a combination of them.