Excel Functions and Formulas: VLOOKUP, IF, PMT, Goal Seek, RANK, Percent Change

VLOOKUP

Use VLOOKUP to look up a value from a table.

VLOOKUP Formula

Type: =VLOOKUP(lookup_value, $table_range$, column_number, FALSE) → Enter.

lookup_value

What you're searching for (like a student's major code).

$table_range$

The data table; lock with $ so it doesn't shift.

column_number

The column to return.

FALSE in VLOOKUP

Indicates an exact match.

VLOOKUP with IF

Use IF to check a condition before doing a VLOOKUP.

IF Statement

Use IF to test a condition and return two different results.

PMT Function

Calculate a monthly loan payment.

PMT Formula

Type: =-PMT(rate/payments_per_year, years*payments_per_year, $loan_amount$)

Goal Seek

Use Goal Seek to solve for an input when you know the desired output.

IF Statement with Absolute Reference

Show tuition only if credits > 0, otherwise show 0.

RANK.EQ Function

Rank values so the highest = 1.

Percent Change

Find percent change between old and new values.

Net Income

Subtract expenses from income.

Cumulative Running Total

Create a running total across months.

Absolute Reference Reminder

When should you use absolute references ($)?

Locking in VLOOKUP

Always lock tables in VLOOKUP.

Locking Constants

Lock constants like tuition values or loan amounts.

Locking Starting Point

Lock the starting point in cumulative totals.

Windows Shortcut for Absolute Reference

Press F4 after typing a cell reference.

Mac Shortcut for Absolute Reference

Press Cmd+T or Fn+F4.