Excel VLOOKUP, HLOOKUP, and Financial Functions: Key Concepts and Formulas

Financial Functions

Functions that can be used to calculate values based on compounded interest ex: taking a loan, investing in a savings account

compound interest

always calculates interest based on the latest amount so, $1000 @ 4% per year for 2 year compounded yearly Year 1: $1000(4%) = $40 Year 2: $1040(4%) = $41.60

simple interest

always calculates interest based on the original amount so, $1000 @ 4% per year for 2 year Year 1: $1000(4%) = $40 Year 2: $1000(4%) = $40

Col_index_num in VLOOKUP

the column number in your table array that contains the corresponding data

Table Array in VLOOKUP

the range or boundary of your table (excluding headings) - leftmost column should contain the table range (must be ascending order if using TRUE in the range-lookup -rightmost column should contain the values you want returned

Table Array in HLOOKUP

the range or boundary of your table (excluding headings) - topmost row should contain the table range (must be ascending order if using TRUE in the range-lookup - last row should contain the values you want returned

Row_index_num in HLOOKUP

the row that contains the corresponding data

The range_lookup argument of the VLOOKUP and HLOOKUP functions

this is an optional argument- if you don't specify, the default is TRUE- an approximate value -TRUE: Finds an exact match or the nest lower value in your table array -FALSE: Finds an exact match in your table table array

Range Lookup Argument

thus us an optional argument - if you don't specify, the default is TRUE - an approximate value

TRUE Range Lookup

- leftmost column (VLOOKUP) or topmost row (HLOOKUP) must be in ascending order - finds an exact match in your table array or finds the lowest value w/o going over -beginning value in the table array must be the lowest value of a lookup value

HLOOKUP Equation

=HLOOKUP (lookup-value, table array, row_index_num, [range_lookup])

IFFEROR Equation

=IFERROR(value, value_if_error) value: any value that may contain an error . If no error, then display value_if_error: if value does contain an error, then display this value

VLOOKUP Equation

=VLOOKUP(lookup_value, table-array, col_Index,num, [range-lookup])

HLOOKUP Function Definition

Finds an entry from a horizontal array based on a criteria

VLOOKUP Definition

Finds an entry from a vertical array based on a criteria. Use when your comparison values are located in a column to the left of the data you want to find.

FALSE Range Lookup

Finds an exact match in your table array

IFFERROR Function definition

Returns a value you specify if a formula evaluates to an error, other wise it returns the result of the formula

Present Value Function

What you get/pay at the beginning of the financial transaction =PV(rate,nper,pmt,[FV],[type])

Compounding Periods

Yearly, Quarterly (4), Semi annually (twice a year), Monthly (12 times per year)

lookup_value in VLOOKUP

Criteria to lookup or "match" (can be a number, text, logical value, or a name or reference that refers to a value)

lookup_value in HLOOKUP

Criteria to lookup or "match" (can be a number, text, logical value, or a name or reference that refers to a value)