Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 4, Virtual 1
Lookup Practice and Data Validation
Two lookups into the Staff Directory on your own, then the other side of clean data: stopping bad values from being typed in the first place.
By the end of this class you can
- Fetch a zone and a manager from the Staff Directory with XLOOKUP
- Check a lookup against a column you built another way
- Explain why a lookup table is easier to maintain than a long IFS
- Build a dropdown list with Data Validation, from typed items or from a range
- Combine a dropdown and XLOOKUP into a small order form
Weeks 2 and 3 cleaned up mistakes after they happened. Data Validation is how you stop them happening. Every " chidi okonkwo" you fixed with PROPER and TRIM was typed by a person into a cell that would have accepted anything.
What you do in this class
- Part 1: look up each sale's zone and manager from the Staff Directory, and check them.
- Part 2: build a dropdown for Category on a new sheet and try to break it.
- Part 3: build the small order form.
Hand in: Sales Data with a working manager lookup, and an Order Form sheet with validated cells.
Part 1: Two Lookups Into the Staff Directory
Every sale carries a Staff_ID (column H). The Staff Directory tab has one row per Staff_ID, with the branch, region, zone, manager and contract of that person.
Lookup 1: Zone
Zone_Lookup agrees with the Zone column you built with IFS in Week 3, on every row. Check it yourself: in Q2 type =P2=O2, fill it down, and filter for FALSE. There are none. Same answer, very different formula. Delete the check column afterwards.
Lookup 2: Manager
Every sale now carries its manager: Amaka Eze, Chioma Obi or Ibrahim Sule. Not one name was typed, so not one can be misspelt. With this column, a SUMIFS can tell each manager what their team sold.
Why the Lookup Table Wins
The Week 3 IFS returns #N/A for Owerri, because nobody wrote an Owerri test into the formula, and it would need editing in every report that uses it. With a lookup table, opening a branch means adding one row to the Staff Directory. Business rules belong in tables, where anyone can see and change them, not buried inside formulas.
Instead of 'Staff Directory'!$A$2:$A$11, you can point at whole columns, 'Staff Directory'!A:A and 'Staff Directory'!E:E. New rows are then included automatically. It is slightly slower on very large sheets, which does not matter at this size.
Picture a phone contact list versus a note glued inside the phone case. Adding a friend to the list takes a second. Changing the glued note means taking the phone apart.
Part 2: Data Validation
Definition
Data Validation is a rule attached to a cell that controls what can be typed into it: only items from a list, only whole numbers in a range, only dates after a certain day. Anything else is refused with a message.
Real-Life Scenario
A bank deposit form with boxes for each digit of your account number, and a list of branches to tick instead of a blank line to write on. The form is designed so that you cannot easily fill it in wrongly. Data Validation designs your spreadsheet the same way.
Types of Validation
| Allow | Use it for | TechPlug example |
|---|---|---|
| List | A fixed set of choices, shown as a dropdown | Category: Accessories, Appliances, Laptops, Smartphones, Televisions |
| Whole number | Counts, with a minimum and maximum | Quantity between 1 and 10 |
| Decimal | Amounts with fractions | A discount between 0 and 0.3 |
| Date | Dates within a range | A sale date in 2024 |
| Text length | Codes of a fixed length | A Product_ID of exactly 7 characters |
Type 1: A Dropdown List
Add a new sheet called Order Form. Type Category in A1 and select B1.
Category dropdown
Now try to break it. Click B1 and type laptops with a space after it, then press Enter.
Refused. The kind of value that made Week 2 necessary cannot get into this cell. (Capitals are the one thing a list ignores: typing LAPTOPS is accepted, because Excel compares list items without caring about case.)
Type 2: A Whole Number Rule, With Your Own Message
Quantity rule
Why 1 to 10? Because no sale in the Sales Data sheet is for more than 10 units: a rule based on what the business really does. The Input Message appears as a small yellow note whenever someone selects B2, before they type anything: prevention, not just refusal. The three error styles behave differently: Stop refuses the value, Warning asks "Continue?", and Information just tells you and accepts it.
Data Validation does not check values pasted into a cell, or values that were there before the rule was added. For existing data, use Data > Data Validation arrow > Circle Invalid Data, which draws a red circle round every cell that breaks the rule.
Part 3: Overall Example, a Small Order Form
Put it together. The dropdown for the product reads its list from the Product Catalogue, so it is always up to date, and XLOOKUP fills in the name and the price.
Order form
Three HP Laptop 15s at N450,000 each: N1,350,000. Nobody typed a name or a price, and nobody can choose a product that does not exist. If procurement changes the price on the Product Catalogue tab, the form updates immediately. This is a tiny version of how real order systems are built.
Add the Quantity rule from Part 2 to B2, then try typing 0, 15 and 2.5. Which ones are refused?
Check your answer
All three. 0 and 15 are outside the range; 2.5 is not a whole number.
Picture the bank form again, with boxes, tick-lists and a clerk who fills in the charges for you. The customer can only make choices that make sense, and the arithmetic is never theirs to get wrong.
Checkpoint
Q1. Why is a Staff Directory lookup better than the Week 3 IFS for zones?
A new branch is one new row in the directory. With IFS, every formula that uses the rule must be edited.
Q2. A dropdown list refuses "laptops " with a space. Does it refuse "LAPTOPS"?
No. List validation ignores capitals, but not spaces.
Q3. Does Data Validation check values that are pasted in?
No. Use Circle Invalid Data to find invalid values already in the sheet.
Q4. What is the difference between the Stop and Warning error styles?
Stop refuses the value. Warning lets the person continue after a warning.
Tick Before You Move On
- ☐ My zone lookup matches the Week 3 Zone column on every row
- ☐ Every sale has its manager, looked up from the Staff Directory
- ☐ I built a dropdown, tried to break it, and saw the error
- ☐ I set a custom input message and error alert
- ☐ My order form fills in the product, the price and the total by itself
Quick Recap
XLOOKUP against a lookup table replaces long IFS rules and is easier to keep up to date. Data Validation controls what can be typed: lists, numbers, dates and text lengths. An input message prevents mistakes; an error alert refuses them. Validation does not check pasted or existing values; Circle Invalid Data does. A dropdown plus a lookup makes a form where wrong entries are hard to make.
Next, Week 4, Live 2: PivotTables. A thousand sales summarised by zone in four drags, a chart that updates with it, a slicer to filter it, and a first look at Power Query.