📄 Save or print this lesson:

← Module 02: Formulas & Functions 🏠 Course Home Module 04: Working with Data →

Module 03 of 08 | Excel & Google Sheets for Data

🤖 Logic & Lookup Functions

Make your spreadsheet think. Use IF to make decisions, COUNTIF and SUMIF to analyze conditionally, and VLOOKUP/XLOOKUP to find information across tables — the skills that turn spreadsheets into databases.

⏰ ~45 minutes 🎓 Beginner–Intermediate 📋 Requires: Module 02

📌 Before You Start

Set up your practice file

Open a new Google Sheet. You'll build two separate tables during this module — one for IF/COUNTIF/SUMIF practice, and one for VLOOKUP/XLOOKUP. Keep this page open alongside your spreadsheet and type each formula as you encounter it. Don't just read — do.

💡 The Concept

IF: Making Decisions in a Cell

The IF function lets your spreadsheet choose between two outcomes based on a condition. It works exactly like plain English: "IF this is true, show this; otherwise show that."

=IF(logical_test, value_if_true, value_if_false)

Comparison Operator	Meaning	Example
`>`	Greater than	`A1 > 70`
`<`	Less than	`A1 < 60`
`>=`	Greater than or equal	`A1 >= 90`
`=`	Equal to	`A1 = "Pass"`
`<>`	Not equal to	`A1 <> "Fail"`

Worked Examples:

=IF(B2>70, "Pass", "Fail") → If B2 is greater than 70, show "Pass"; otherwise show "Fail" =IF(B2>=90, "A", "Not A") → Simple grade check =IF(C2>0, C2*0.1, 0) → If C2 is positive, calculate 10% of it; otherwise 0

Nesting IF for letter grades:

=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))

This is called a nested IF — one IF inside another. It checks conditions from highest to lowest. However, for more than 3 conditions, use IFS instead (it's much cleaner).

🔧 Step-by-Step

IFS: Multiple Conditions Without the Nesting Nightmare

IFS lets you check multiple conditions in sequence without nesting IF inside IF inside IF. Each pair of arguments is: (condition, result_if_true).

=IFS(condition1, result1, condition2, result2, ...)

=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", TRUE, "F") ← The final TRUE acts as a catch-all "else"

Much cleaner than four nested IFs. The TRUE at the end is a trick — since TRUE is always true, it catches everything that didn't match earlier conditions (like an "else" clause).

Excel Note: IFS is available in Excel 2019+. In older Excel versions, you must use nested IF or the CHOOSE + MATCH approach.

📋 Sample Data — Sales Table

Build This Table for COUNTIF & SUMIF Practice

A — Category	B — Product	C — Revenue
Electronics	Laptop	1200
Electronics	Tablet	450
Books	Python Handbook	35
Clothing	Jacket	89
Electronics	Headphones	120
Books	Statistics Guide	45
Clothing	Sneakers	110
Electronics	Phone Case	25

💡 The Concept

COUNTIF and SUMIF: Analyze with Conditions

COUNTIF — Count cells that meet a condition

=COUNTIF(range, criteria)

=COUNTIF(A2:A9, "Electronics") → How many Electronics rows? (Answer: 4) =COUNTIF(C2:C9, ">100") → How many sales over $100? =COUNTIF(C2:C9, "<50") → How many sales under $50?

The criteria must be in quotes if it's text or contains a comparison operator.

SUMIF — Sum cells that meet a condition

=SUMIF(range, criteria, sum_range)

=SUMIF(A:A, "Electronics", C:C) → Sum of Revenue where Category = "Electronics" → Answer: 1200+450+120+25 = 1795 =SUMIF(C:C, ">100", C:C) → Sum of all sales greater than $100 =SUMIF(A:A, "Books", C:C) → Total Books revenue

The three arguments are: (1) the column you're checking for the condition, (2) what condition to match, (3) the column you want to add up when the condition is met.

COUNTIFS and SUMIFS — Multiple conditions at once

Add an "S" to handle multiple criteria at once:

=COUNTIFS(A:A, "Electronics", C:C, ">100") → Count rows where Category=Electronics AND Revenue>100 =SUMIFS(C:C, A:A, "Electronics", C:C, ">100") → Sum Revenue where Category=Electronics AND Revenue>100

📋 Sample Data — Product Lookup Table

Build This Table for VLOOKUP Practice (on a new sheet)

Create a new sheet tab. Put your main product table starting at A1, and a lookup area starting at E1:

Main table (A1:C6):

A — Code	B — Product	C — Price
P001	Laptop Stand	$29.99
P002	USB Hub	$15.50
P003	Webcam	$79.00
P004	Keyboard	$49.99
P005	Mouse Pad	$8.99

Lookup area (E1:F2):

E — Look up code:	F — Result:
P003	(formula goes here)

💡 The Concept

VLOOKUP: Find a Value in a Table

VLOOKUP looks up a value in the first column of a table, then returns a value from another column in the same row. It's like saying: "Find P003 in my product list, and tell me the price."

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

Argument	What it means	In our example
`lookup_value`	What to search for	`E2` (the code P003)
`table_array`	The table to search	`A:C` (columns A through C)
`col_index_num`	Which column to return (1=A, 2=B, 3=C)	`3` for Price (column C)
`FALSE`	Exact match (always use FALSE for codes/IDs)	`FALSE`

In cell F2, type:

=VLOOKUP(E2, A:C, 3, FALSE) → Looks up value in E2 (P003) in column A → Returns the value from column 3 (Price: $79.00)

Change E2 to "P001" and F2 automatically updates to show $29.99. That's the power of lookup functions.

Common VLOOKUP gotcha: VLOOKUP only searches the first column of the table array. Your lookup column must be on the left. If you need to look up by a column that isn't leftmost, use XLOOKUP instead.

🔧 Step-by-Step

XLOOKUP: The Modern Replacement for VLOOKUP

XLOOKUP is newer, simpler, and more flexible than VLOOKUP. It can look left or right, and the syntax is cleaner.

=XLOOKUP(lookup_value, lookup_array, return_array)

Simple version (3 arguments): =XLOOKUP(E2, A2:A6, C2:C6) → Find E2 in A2:A6, return the matching value from C2:C6 With a "not found" message (4th argument): =XLOOKUP(E2, A2:A6, C2:C6, "Code not found") → If the code doesn't exist, show "Code not found" instead of an error

Feature	VLOOKUP	XLOOKUP
Lookup direction	Left-to-right only	Any direction
Column index	Must count manually	Specify the range directly
"Not found" handling	Requires IFERROR wrapper	Built-in 4th argument
Return multiple columns	One at a time	Can return a range
Availability	All versions	Excel 365, Google Sheets (2020+)

Recommendation: Use XLOOKUP for new work. Use VLOOKUP when sharing with people who might have older Excel versions.

🖐 Your Turn

Exercise: Grade Book + Lookup System

Part 1 — Grade Book with IF and IFS:

Use the 10-student grades dataset from Module 02 (or recreate it). Add a column E called "Letter Grade". In E2, write an IFS formula that assigns:

A if average ≥ 90
B if average ≥ 80
C if average ≥ 70
D if average ≥ 60
F if below 60

Copy it down for all 10 students.

Part 2 — Summary stats with COUNTIF and SUMIF:

In a summary area, answer these questions with formulas:

How many students got an "A"? (use COUNTIF on the Letter Grade column)
How many students scored above 80 on the Midterm? (COUNTIF)
What is the total Final score for students who got an "A"? (SUMIF)

Part 3 — VLOOKUP:

Create a small product table (5 products with Code, Name, Price). Then in a separate area, create a "lookup cell" where you type a product code and a VLOOKUP formula returns the price automatically.

Hint for IFS: Remember to add TRUE, "F" as the final pair so students below 60 get an F.

Hint for VLOOKUP: Make sure your Code column is the first column in your table_array, and double-check your col_index_num (Price should be column 3 if Code=1, Name=2, Price=3).

🧠 Brain Break

Why is VLOOKUP So Famous?

VLOOKUP is one of the most searched Excel functions on the internet — and it's also one of the most complained about. People struggle with the column index number (you have to count manually), the "lookup must be in first column" restriction, and cryptic #N/A errors. Microsoft surveyed Excel users and found VLOOKUP was both beloved and frustrating — a classic "love-hate" relationship.

That's why Microsoft introduced XLOOKUP in 2019 and why Google added it to Sheets in 2020. VLOOKUP isn't going away — there are billions of spreadsheets using it — but for new work, XLOOKUP is strictly better. Think of VLOOKUP as the legacy system and XLOOKUP as the upgrade.

✅ Wrap Up

Module 03 Key Takeaways

IF(condition, true_result, false_result) — makes a binary decision in a cell.
IFS handles multiple conditions cleanly — end with TRUE, "else_value".
COUNTIF(range, criteria) — counts cells matching a condition.
SUMIF(range, criteria, sum_range) — sums cells where another column matches a condition.
VLOOKUP(value, table, col_num, FALSE) — finds a value in the first column of a table and returns another column from the same row.
XLOOKUP(value, lookup_range, return_range) — more flexible modern lookup; preferred for new work.
Always use FALSE (exact match) in VLOOKUP when looking up codes or IDs.

Next: Module 04 — Working with Data →