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

VLOOKUP

Use VLOOKUP to return a Major's description.

VLOOKUP Steps

Click E12. Type: =VLOOKUP(D12,$I$21:$L$24,2,FALSE) → Enter.

D12

Lookup value (Major Code).

$I$21:$L$24

Locked lookup table (absolute reference — use F4 on Windows or Cmd+T on Mac).

2

Column with descriptions.

FALSE

Exact match.

VLOOKUP with IF

Return 'Bus. Adv. Office' if Freshman, else Advisor from table.

PMT Function

Calculate monthly mortgage payment.

PMT Steps

Click the target cell (e.g., B30). Type: =-PMT(B29/B28,B27*B28,B26) → Enter.

B29

Annual rate ÷ B28 (payments per year).

B27*B28

Total number of payments.

B26

Loan amount.

Goal Seek

Use Goal Seek to find the monthly contribution needed to reach $5,000.

Goal Seek Steps

Go to Data tab → What-If Analysis → Goal Seek.

IF Statement

Show tuition if B20 ≠ 0, else 0.

IF Steps

Click B19. Type: =IF(B20<>0,$B$33,0) → Enter.

IF + Comparison Example

Return 'Pass' if grade ≥ 60, else 'Fail.'

RANK.EQ

Rank SAT scores, highest = 1.

Percent Change

Calculate percent change from Jan to Feb salary.

Net Income

Find monthly net income.

Cumulative Net Income

Running total from Jan → current month.

VLOOKUP Reminder

What are the 4 arguments of VLOOKUP?

PMT Reminder

What are the 3 arguments for PMT?