DATA 110 — Interactive Lab

Chapter 5: Function Logic

Master Excel's decision-making and lookup functions through hands-on practice with real business scenarios.

Score
0
Streak
0 🔥
Completed
0 / 20
🧩 Formula Builder
Read the business scenario, then drag the correct function parts into the formula bar to build a working formula.
0 / 5
Question 1 of 5
Business Scenario
Formula Bar
Available Parts — Drag into the blanks above
🎯 Scenario Solver
A business problem is described. Choose the correct formula that solves it.
0 / 5
Question 1 of 5
Business Problem
🔀 Argument Order
Drag the arguments into the correct order for each function. Understanding argument position is critical for writing correct formulas.
0 / 5
Question 1 of 5
🔮 Output Predictor
Given a dataset and a formula, predict what value Excel will return. Think through the logic step by step.
0 / 5
Question 1 of 5
📖 Chapter 5 Function Quick Reference

IF( logical_test, value_if_true, value_if_false )

=IF(C5>=50, "Certified", "Not Certified")

Makes a decision: if the test is TRUE, returns one value; if FALSE, returns another. The backbone of conditional logic in Excel.

IFS( test1, value1, test2, value2, ... )

=IFS(C14>=40, "Expert", C14>=25, "Proficient", TRUE, "Beginner")

Evaluates multiple conditions in order. Returns the value paired with the first TRUE condition. Use TRUE as the last test for a default/"else" case.

Nested IF with AND / OR

=IF(AND(F10="Yes", OR(G10="Yes", H10>100)), 0.1, "")

AND requires all conditions to be true. OR requires at least one. Nest them inside IF to create complex multi-condition decision logic.

VLOOKUP( lookup_value, table_array, col_index, [range_lookup] )

=VLOOKUP(B10, Product_List, 4, FALSE)

Searches the first column of a table for a value, then returns a value from a specified column in the same row. FALSE = exact match; TRUE = approximate match.

HLOOKUP( lookup_value, table_array, row_index, [range_lookup] )

=HLOOKUP(E10, $B$4:$D$6, 2, FALSE)

Like VLOOKUP but searches horizontally across the first row, then returns a value from a specified row. Useful when data tables are arranged in rows.

XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found] )

=XLOOKUP(B2, StudentID, SkillLevel, "Not Found")

The modern replacement for VLOOKUP/HLOOKUP. Searches any column/row and returns from any other. Has a built-in error handler. Defaults to exact match.

INDEX( array, row_num, [col_num] ) + MATCH( value, range, type )

=INDEX($A$4:$G$16, MATCH(MAX($E$4:$E$16), $E$4:$E$16, 0), MATCH(A20, $A$3:$G$3, 0))

INDEX returns a value at a specific row/column position. MATCH finds the position of a value. Together they form a powerful, flexible lookup that can search in any direction.

IFERROR( value, value_if_error )

=IFERROR(VLOOKUP(B10, Product_List, 4, FALSE)*D10, "")

Wraps any formula — if it returns an error (#N/A, #VALUE!, etc.), IFERROR catches it and returns your fallback value instead. Essential for clean, user-friendly spreadsheets.

INDIRECT( ref_text )

=VLOOKUP(I5, INDIRECT(I4), MATCH(I6, A4:F4, 0), TRUE)

Converts a text string into an actual cell reference or named range. Enables dynamic table selection — for example, switching between "Priority" and "Express" shipping tables based on a cell value.

INDEX + MATCH + INDIRECT (Combined)

=INDEX(INDIRECT(D4), MATCH(B4, $G$14:$G$17, 0), MATCH(C4, $H$13:$L$13, 1))

The ultimate dynamic lookup: INDIRECT selects which table to search, the first MATCH finds the row, and the second MATCH finds the column. INDEX then retrieves the value at that intersection.