← Data Analytics from Scratch: SQL, Spreadsheets and Metrics
Lesson
Conditions and lookups: IF, COUNTIF, VLOOKUP
Learner can write IF and COUNTIF conditions and a VLOOKUP to pull a value from another table, and explain the common VLOOKUP pitfalls.
Conditional logic and table lookups
Conditional logic and table lookups
IF lets you return different values depending on whether a condition is true or false. The syntax is: =IF(condition, value_if_true, value_if_false). For example, =IF(B2>=100, "Target met", "Below target") checks whether B2 is at least 100 and displays one of two labels. Conditions use comparison operators: >, <, >=, <=, = and <> (not equal).
COUNTIF counts how many cells in a range match a criterion you specify. Syntax: =COUNTIF(range, criterion). The criterion can be a number, text or a comparison string in quotes. For example, =COUNTIF(B2:B6, ">=100") counts how many values in B2:B6 are 100 or above. Using our verified sales dataset [120, 80, 200, 80, 150], the values that meet >=100 are 120, 200 and 150 — so the result is 3. Notice that 80 and 80 do not qualify.
VLOOKUP lets you search a lookup table and return a value from a specific column. Syntax: =VLOOKUP(lookup_value, table_range, col_index, [exact_match]). Always pass FALSE as the last argument for exact matching — omitting it or passing TRUE uses approximate match, which can return wrong results when the table is not sorted. Two critical rules: (1) the key column you are searching must be the leftmost column of your table_range; (2) col_index counts from 1 starting at the leftmost column of the range, not from column A of the sheet. Example: =VLOOKUP("London", A2:C10, 3, FALSE) searches column A for "London" and returns the value in the third column of the range A2:C10.
Both Excel and Google Sheets support all three functions with identical English names. XLOOKUP is available in newer versions but VLOOKUP remains universal.
Lesson notes
Conditional logic and table lookups
IF lets you return different values depending on whether a condition is true or false. The syntax is: =IF(condition, value_if_true, value_if_false). For example, =IF(B2>=100, "Target met", "Below target") checks whether B2 is at least 100 and displays one of two labels. Conditions use comparison operators: >, <, >=, <=, = and <> (not equal).
COUNTIF counts how many cells in a range match a criterion you specify. Syntax: =COUNTIF(range, criterion). The criterion can be a number, text or a comparison string in quotes. For example, =COUNTIF(B2:B6, ">=100") counts how many values in B2:B6 are 100 or above. Using our verified sales dataset [120, 80, 200, 80, 150], the values that meet >=100 are 120, 200 and 150 — so the result is 3. Notice that 80 and 80 do not qualify.
VLOOKUP lets you search a lookup table and return a value from a specific column. Syntax: =VLOOKUP(lookup_value, table_range, col_index, [exact_match]). Always pass FALSE as the last argument for exact matching — omitting it or passing TRUE uses approximate match, which can return wrong results when the table is not sorted. Two critical rules: (1) the key column you are searching must be the leftmost column of your table_range; (2) col_index counts from 1 starting at the leftmost column of the range, not from column A of the sheet. Example: =VLOOKUP("London", A2:C10, 3, FALSE) searches column A for "London" and returns the value in the third column of the range A2:C10.
Both Excel and Google Sheets support all three functions with identical English names. XLOOKUP is available in newer versions but VLOOKUP remains universal.