BUA 345 - Lecture 6
Data Queries
2026-01-28
💥 Lecture 6 In-class Exercise - Q1-Q2 💥
Poll Everywhere - My User Name: penelopepoolereisenbies685
The Pivot Table below shows the COUNTS of Titanic passengers in each class that did or did not survive the disaster.
Question 1. If we want to know the percent of passengers that survived (Yes) that were in second class, what option should we select for Show Values As?
Should we select % of Column Total, % of Row Total or % of Grand Total?
Question 2. If we want to know the percent of ALL passengers that were in third class and did not survive, what option should we select for Show Values As?
Data Set Queries
A useful skill is to be able to find specific observations quickly in a large database.
Excel facilitates this process with these commands:
- VLOOKUP
- MATCH
You are working for a Non-profit that offers celebrity autographs in exchange for donations, so you need to contact celebrities.
This first worksheet only has 5 observations, but what if it had 200?
You want to find a phone number quickly, just based on STAGE NAME.
- We will create a
VLOOKUPcommand to do this.
VLOOKUP Command
We will use VLOOKUP to automate a datebase search.
We enter Bad Bunny in the cell next to ‘STAGE NAME’
We create a
VLOOKUPfunction to output correct phone number in cell next to ‘FAN PHONE NO.’
VLOOKUP inputs
G2: Where reference value is entered (see Worksheet 1)B2:D6: Data range to be searchedNote: VLOOKUP requires that Column with reference value is FIRST COLUMN (Index = 1)
STAGE NAME (Column B) is first column
3: Column index where query answer is.- FAN PHONE NO is 3rd column counting from STAGE NAME
FALSE: We enter FALSE to indicate we want an EXACT MATCH
VLOOKUP Example 2
Now you have a different database (Worksheet 2) where STAGE NAME is listed in COLUMN A.
Using this new database, we want to find Rihanna’s phone number
Updating the inputs
G2: Where reference value is entered (see Worksheet 1)??:???: Data range to be searchedNote: VLOOKUP requires that Column with reference value is FIRST COLUMN (Index = 1)
STAGE NAME (Column A) is first column
?: Column index where query answer is.- FAN PHONE NO is 4th column counting from STAGE NAME
FALSE: We enter FALSE to indicate we want an EXACT MATCH
💥 Lecture 6 In-class Exercise - Q3 💥
Poll Everywhere - My User Name: penelopepoolereisenbies685
We want to use
=VLOOKUP(G2, ?, ?, FALSE) to find Rihanna’s phone number in the database in Worksheet 2.
What should the missing inputs be?
HINT: There are two correct choices and either choice will receive full credit.
…, B2:D6, 3, ……, A1:D6, 4, ……, A2:D6, 3, ……, A2:D6, 4, ……, B1:D6, 3, …
VLOOKUP
Error without Data Validation
Using the Worksheet 2 Database, we want to find Beyonce’s phone number phone number, but there’s a common problem.
The accent in Beyonce’s name causes an error.
How do we deal with this?
We use ‘Data Validation’ to restrict options for
G2to ONLY VALID entries.
- Without Data Validation:
Adding Data Validation
Click cell
G2Select the
Data Validationicon on theDatatab.
- Complete the pop-box as shown and click
OK.
- Now cell G2 has a drop-down menu of correct values.
Another Data Validation Example
Worksheet 3 contains Syracuse weather data from October 2025 to January 27, 2026.
NOTE: Data were aggregated from the National Weather Service and values of ‘T’ (trace amounts) were replaced with 0 for this example.
We want to create a query that will output a value when the user specifies a date AND a weather variable.
Our first
VLOOKUPcommand will have to be manually changed if we want to change the weather variable, e.g from snowfall to maximum temperature.We can automate the table functionality with data validation and an embedded
MATCHcommand.
Data Validation
Next steps are to add data validation to cells H2 and H3
We want a pull-down menu in H2 so that only one of the dates can be selected.
We want a pull-down menu in H3 so that only one of the weather variables can be chosen.
Cell H2 Data Validation:
Cell H3 Data Validation:
💥 Lecture 6 In-class Exercise - Q4 💥
Poll Everywhere - My User Name: penelopepoolereisenbies685
The example VLOOKUP command on slide 12 is set up to show the snowfall for different dates.
If we want to know the minimum temperature for different dates, what would change in our VLOOKUP command?
HINT: The third input before FALSE is the only input that needs to change.
3rd input would change from 5 to 1.
3rd input would change from 5 to 2.
3rd input would change from 5 to 3.
3rd input would change from 5 to 4.
How do we correct the incorrect VLOOKUP below?
Match Command
- We can automate the column choice by adding a
MATCHcommand
How do we AUTOMATE the lookup column in the VLOOKUP below? We want the command to update automatically when we choose a new weather variable?
** We REPLACE the third input with a MATCH command:**
- H3: Input value we want to match
- A1:E1: Array of cells to search for input value
- 0: Type of match. 0 indicates EXACT MATCH
VLOOKUP with Range Option
- We begin this example by creating a range lookup table.
- Here are the ranges for Hurricane categories.
We need to convert these to ranges that Excel can understand.
Range lookup will only work with lower limits in ASCENDING ORDER.
- Here are the CORRECT inputs for a Range Lookup table for Hurricane categories.
- Update the table in columns D and E on Worksheet 4.
Putting all the Skills Together
In Cell
B1, we will add data validation so that we have a pull-down list of Hurricane names.In Cell
B2, we will create aVLOOKUPcommand that outputs the maximum wind speed.In cell
B3we will create aVLOOKUPwith a range option that will output the category of the hurricaine.