Microsoft Excel 2021365 - Beginner to Advanced - Exercise 09 - VLOOKUP formula

This video tutorial covers the use of VLOOKUP and logical functions in Excel to manage and analyze data. It includes exercises on formatting data as tables, using VLOOKUP to find test scores and grades, applying logical functions to select venues, and using text functions for data cleaning. The tutorial provides practical examples and step-by-step guidance to ensure understanding and application of these functions.

What is the primary purpose of using VLOOKUP in the Test Scores worksheet?

• To create a data validation list

• To calculate the average score of students

• To format the data as a table

• To retrieve test scores for specific subjects

How can you test if the VLOOKUP formula is working correctly in the Test Scores worksheet?

• By changing the table name

• By selecting different students from a drop-down list

• By modifying the formula to include more subjects

• By adding more columns to the table

What is the main advantage of using named ranges in Excel formulas?

• They make formulas easier to read and manage

• They automatically update data

• They allow for conditional formatting

• They increase the speed of calculations

In the Student Grades worksheet, what should the VLOOKUP formula return if an error occurs?

• The text 'Error'

• The text 'Fail'

• The average score

• The highest score

What logical function is used to determine if a venue can accommodate more than 400 people?

• IF

• OR

• NOT

• AND

Which function is combined with IF to check multiple conditions for venue selection?

• OR

• AND

• NOT

• XOR

What is the purpose of using the TRIM function in the customer data table?

• To replace text with a specific value

• To find the length of the text

• To remove extra spaces from text

• To convert text to uppercase

How can you separate first and last names using Excel features?

• Using the MID function

• Using Flash Fill

• Using the LEFT function

• Using the CONCATENATE function

What is the result of using the PROPER function on a text string?

• Reverses the text string

• Converts all text to lowercase

• Capitalizes the first letter of each word

• Converts all text to uppercase

Which function is used to extract the last four digits of an order number?

• LEN

• RIGHT

• MID

• LEFT