Master Key Excel Formulas for Effective Data Analysis
To unlock the full potential of your Excel reports, mastering key formulas like VLOOKUP, XLOOKUP, INDEX/MATCH, and FILTER is essential. Let’s dive into these powerful tools that can elevate your data analysis.
Let’s take a look at XLOOKUP (Flexible Lookup).
Purpose: A newer alternative to VLOOKUP and HLOOKUP that searches a range both vertically and horizontally. Should be used in place of vlookup and hlookup.
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]
Required Arguments:
lookup_value: The value you are searching for.lookup_array: The range of cells to search within.return_array: The range of cells from which to return a value.
Optional Arguments:
if_not_found: The value to return if no match is found (defaults to#N/A).match_mode: Controls the type of match (e.g., exact, greater than or less than).search_mode: Controls how the search is performed (e.g., first to last, last to first, binary search).
Example:
You have a table of products and their prices.
| Product ID | Product Name | Price |
|---|---|---|
| 101 | Laptop | $900 |
| 102 | Tablet | $450 |
| 103 | Phone | $700 |
You want to find the price of the “Tablet” using its Product ID:
=XLOOKUP(A2, A2:A4, C2:C4, ” “, 0)
=XLOOKUP(The value you want to match, the range that you will find it’s match, the range that the return value is in, what to return if value not found, match mode, 0 by default)
Explanation:
- A2 → The lookup value.
A2:A4→ The column where you want to search for Product ID.C2:C4→ The column where the return value is, our example this is the price column.- ” ” → If not found return whatever is between the quotes.
- 0, 1, -1 → The match mode. 0 exact, 1 next value greater, -1 first value lower.
Returns: $450
Why Use XLOOKUP?
- No column index needed.
- Searches both left to right and right to left.
- Has built-in error handling (with the optional 4th argument).
XLOOKUP can also return multiple values dynamically.