Microsoft Excel 2021365 - Beginner to Advanced - Performing Two-Way Lookups

This video tutorial covers advanced lookup and reference functions in Excel, focusing on two-way lookups using INDEX, MATCH, and the new XLOOKUP function. It explains how to prepare data, create data validation lists, and construct formulas for efficient data retrieval based on two criteria: month and company. The tutorial provides step-by-step guidance on using these functions to perform accurate and efficient lookups in Excel.

What is the primary purpose of a two-way lookup?

• To find a value using two criteria

• To filter data based on a condition

• To find a value using a single criterion

• To sort data in ascending order

What is the first step in preparing data for a two-way lookup?

• Creating named ranges

• Converting data into an Excel table

• Using the XLOOKUP function

• Applying conditional formatting

Why is it beneficial to create named ranges for your data?

• To enable conditional formatting

• To apply filters more efficiently

• To automatically sort the data

• To make formulas easier to understand

Which function is used to automate the finding of the row number in a two-way lookup using INDEX and MATCH?

• SUM

• VLOOKUP

• HLOOKUP

• MATCH

What is a key difference between INDEX and MATCH and XLOOKUP for two-way lookups?

• INDEX and MATCH are faster

• XLOOKUP specifies the return array last

• INDEX and MATCH can only handle one criterion

• XLOOKUP requires more steps

In the context of XLOOKUP, what does the 'return array' refer to?

• The range of the lookup criteria

• The range of the entire dataset

• The range where the result is returned from

• The range where the lookup value is found

What additional feature does XLOOKUP offer compared to INDEX and MATCH?

• Ability to specify a value if not found

• Ability to handle more than two criteria

• Ability to automatically create named ranges

• Ability to search from last to first