Lookups and Database Functions
Learning Outcomes
Get More Info
data, Tech & ai skills in
What’s Included
Finding Records with VLOOKUP
Finding Records with VLOOKUP
VLOOKUP is a popular command for finding unique records within a dataset. Its awkward syntax does take some practice however.
Naming Arrays
Naming Arrays
Naming arrays saves you buckets of time when writing formulas and has the happy knack of making them much more readable.
Better Lookups with INDEX/MATCH
Better Lookups with INDEX/MATCH
Combine the INDEX and MATCH commands to create more flexible and faster lookups. Essential for budding Excel power users
Using XLOOKUP
Using XLOOKUP
XLOOKUP is a modern alternative to VLOOKUP and INDEX/MATCH. Learn how to use it, and why it's so useful, in this lesson.
Tiered Pricing with Nested IF
Tiered Pricing with Nested IF
We often want to split our data into different tiers, with pricing a common example. Nested IF statements help us complete this task
Create a Dropdown List for a Search Panel
Create a Dropdown List for a Search Panel
We all love dropdown lists on the web so why not use them in Excel? In this lesson we create a dropdown list of our sales reps which automatically return their total annual sales
Ranking Data Records
Ranking Data Records
I'd like to extract the top 5 largest customers for each sales rep, and to do this we'll use the LARGE function in Excel.
Lookup Multiple Criteria
Lookup Multiple Criteria
Learn how to lookup multiple criteria in this lesson by combining INDEX and MATCH with array formulas.
Calculating Monthly Totals
Calculating Monthly Totals
Create a search panel that allows you to pick a month from a dropdown list and returns the total revenue for that particular month.
Database Functions
Database Functions
The relatively unknown database functions are great for quickly looking up multiple criteria. Learn how to use them in this lesson.
Updating Formulas with New Data
Updating Formulas with New Data
When you add additional records to a dataset, you don't have to update all the formulas each time. Instead, you can automatically grow the arrays when new records are added.
Updating Formulas with New Data Part 2
Updating Formulas with New Data Part 2
To finish off the course, we update our search panel formulas to automatically include any new company records added to our sales dataset.
Certifications
A Learner Experience Designed to Deliver Transformation
How Leading Teams Use Kubicle to Upskill & Excel
