Excel Lookup Functions: XLOOKUP, FILTER, XMATCH, LOOKUP, and SWITCH - Part 6

Overview

Coursera Plus Annual Sale: All Certificates & Courses 25% Off!

Grab it

Learn essential Excel lookup functions in this comprehensive 49-minute video tutorial that covers XLOOKUP, FILTER, XMATCH, LOOKUP, and SWITCH functions. Master the powerful XLOOKUP function for performing various lookup tasks, understand when to use alternative lookup functions, and explore practical examples including product pricing, employee records, tax calculations, and sales data analysis. Discover advanced techniques like two-way lookups, wildcard searches, and handling duplicate values in lookup arrays. Practice with downloadable Excel files and PDF notes while exploring topics such as data validation, binary search operations, custom number formatting, and performance comparisons between different lookup methods. Complete hands-on exercises to reinforce learning and gain confidence in solving complex lookup challenges in Excel.

Syllabus

1. Introduction
2. Topics in video
3. Problems with AI and Excel
4. XLOOKUP arguments
5. Exact Match Lookup to lookup a product price
6. Data Validation to add a dropdown list to a cell to facilitate accurate XLOOKUP results
7. Different types of Data Validation
8. How XLOOKUP can be better than XLOOKUP
9. Lookup an employee record whole row with XLOOKUP
10. Lookup whole column of days sales with XLOOKUP to then add with SUM function
11. Exact Match or Next Smaller lookup
12. Characteristics of tax, commission, sales discount, and other similar lookup tables that lead us to use the LOOKUP function rather than the XLOOKUP function
13. Comprehensive look at how XLOOKUP and LOOKUP perform and handle Exact Match or Next Smaller lookup, also known as Approximate Match Lookup.
14. Characteristics of tax table leads to using LOOKUP function, even though XLOOKUP can do some crazy tricks
15. LOOKUP function arguments
16. How LOOKUP Binary Search works
17. Use LOOKUP function on same example as XLOOKUP
18. Binary option in XLOOKUP is not as fast as LOOKUP
19. XLOOKUP settings to simulate exact return results as XLOOKUP, but is very slow
20. Timing results of LOOKUP and XLOOKUP
21. XLOOKUP and LOOKUP function algorithms to see how the binary options in each can yield different answers
22. XLOOKUP can perform Vertical or Horizontal Lookup
23. XLOOKUP with Exact Match or Next Bigger to lookup pipe size for land drainage pipes
24. Custom Number Formatting to show numbers in inches
25. Wildcard lookup with XLOOKUP to do a partial text lookup
26. First-To-Last and Last-To-First search_mode options
27. First-To-Last and Last-To-First search yield same results when there are no duplicates in the lookup_array
28. What happens when there are duplicates in the lookup_array?
29. Lookup First-To-Last when there are duplicate values in lookup_array. See example of looking up employees first day of work.
30. Lookup Last-To-First when there are duplicate values in lookup_array. See example of looking up employees last sales amount.
31. XLOOKUP to use two lookup values to lookup a City and Product Price
32. Two-Way lookup with the XLOOKUP function. Example of getting a tax rate for employee with weekly wage and number of allowances
33. FILTER function to return multiple items from one lookup value.
34. XMATCH function to compare two lists
35. SWITCH function to lookup three different lookup tables inside the LOOKUP function
36. Homework – Practice Problems for you!
37. Summary
38. Closing