Analyzing and Looking Up Data

Mastering Cell References (Relative vs. Absolute)

Before we dive into more complex functions, we must understand how Excel handles cell references when you copy formulas. This is a critical concept.

Relative Reference (e.g., A1): This is the default. When you copy a formula with a relative reference, the reference adjusts itself based on the new location.
Example: If you copy a formula from C1 that refers to A1 down to C2, the formula will automatically change to refer to A2.
Absolute Reference (e.g., $A$1): This reference is “locked” and will not change no matter where you copy the formula. The dollar sign $ locks the column ($A) and the row ($1).
Example: This is essential when you have a single cell (like a tax rate or a commission percentage) that must be used in calculations across many rows.
Mixed Reference (e.g., A$1 or $A1): This locks either the column or the row, but not both.
Example: Useful for more advanced scenarios, like creating a multiplication table.

Pro Tip:
While editing a formula, you can press the F4 key to cycle through the four reference types for the cell address your cursor is on (A1 → $A$1 → A$1 → $A1).

Summarizing Data with Conditional Functions

These functions allow you to count or sum cells that meet specific criteria.

COUNTIF and COUNTIFS

COUNTIF: Counts the number of cells within a range that meet a single criterion.
Syntax:
```
=COUNTIF(range, criterion)
```
Example: To count how many employees are in the “Sales” department (data in column C):
```
=COUNTIF(C:C, "Sales")
```
COUNTIFS: Counts cells that meet multiple criteria across different ranges.
Syntax:
```
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], ...)
```
Example: To count how many are in “Sales” (column C) AND are “Full Time” (column D):
```
=COUNTIFS(C:C, "Sales", D:D, "Full Time")
```
Note: All ranges in COUNTIFS must have the same size and shape.

SUMIF and SUMIFS

SUMIF: Adds the cells in a range that meet a single criterion.
Syntax:
```
=SUMIF(range, criterion, [sum_range])
```
Example: To sum the salaries (column E) for everyone in the “Sales” department (column C):
```
=SUMIF(C:C, "Sales", E:E)
```
SUMIFS: Adds cells that meet multiple criteria.
Syntax:
```
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)
```
Notice the sum_range comes first in SUMIFS!

Example: To sum salaries (column E) for “Sales” (column C) who are “Full Time” (column D):
```
=SUMIFS(E:E, C:C, "Sales", D:D, "Full Time")
```

Looking Up Data with VLOOKUP

VLOOKUP (Vertical Lookup) is a cornerstone function for finding a value in the first column of a table and returning a corresponding value from another column in the same row.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value you are looking for.
table_array: The table range where you will search.
Crucially: The column containing the lookup_value must be the first column of this range.
It’s common to lock this range with absolute references (e.g., $A$2:$D$100).
col_index_num: The column number within your table_array from which to retrieve the result. The first column is 1, the second is 2, and so on.
range_lookup: This tells Excel whether to find an exact or approximate match.
- FALSE: Exact match. This is what you will use 99% of the time. If no exact match is found, Excel returns an error.
- TRUE: Approximate match. Used for finding values within ranges (like tax brackets). The first column of your table_array must be sorted in ascending order for this to work correctly.

Example:
You have a product ID in cell A2. You want to find its price from a product table located in $L$2:$N$19, where the price is in the 3rd column.

=VLOOKUP(A2, $L$2:$N$19, 3, FALSE)

Handling Errors Gracefully with IFERROR

Sometimes, a VLOOKUP can’t find a match and returns an ugly #N/A error. You can provide a more user-friendly result by wrapping your formula in the IFERROR function.

Syntax:

=IFERROR(value, value_if_error)

Example:
If the VLOOKUP from the previous example results in an error, you can make it show a blank cell or a custom message instead.

=IFERROR(VLOOKUP(A2, $L$2:$N$19, 3, FALSE), "Not Found")

This formula will try the VLOOKUP. If it works, you get the price. If it fails, you get the text “Not Found”.