Download Excel File: https://people.highline.edu/mgirvin/AllClasses/218M365/Content/ExcelBasics06.xlsx
Read (download right-click): pdf notes: https://people.highline.edu/mgirvin/AllClasses/218M365/Content/ExcelBasics06.pdf
In this video learn about the most amazing lookup function in Excel: XLOOKUP Function, which can do 10 or more lookup tasks. Also learn about when you must use other lookup functions such as: LOOKUP, FILTER, SWITCH and XMATCH. Also see extensive examples of why for approximate match lookup, you should use the LOOKUP function, not XLOOKUP!
Topics:
1. (00:00) Introduction
2. (00:31) Topics in video
3. (01:26) Problems with AI and Excel
4. (04:28) XLOOKUP arguments
5. (05:27) Exact Match Lookup to lookup a product price
6. (07:26) Data Validation to add a dropdown list to a cell to facilitate accurate XLOOKUP results
7. (08:03) Different types of Data Validation
8. (09:01) How XLOOKUP can be better than XLOOKUP
9. (10:17) Lookup an employee record (whole row) with XLOOKUP
10. (11:58) Lookup whole column of days sales with XLOOKUP to then add with SUM function
11. (13:52) Exact Match or Next Smaller lookup
12. (14:48) 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. (15:36) Comprehensive look at how XLOOKUP and LOOKUP perform and handle Exact Match or Next Smaller lookup, also known as Approximate Match Lookup.
14. (17:40) Characteristics of tax table leads to using LOOKUP function, even though XLOOKUP can do some crazy tricks
15. (18:06) LOOKUP function arguments
16. (18:52) How LOOKUP Binary Search works
17. (19:42) Use LOOKUP function on same example as XLOOKUP
18. (20:20) Binary option in XLOOKUP is not as fast as LOOKUP
19. (21:02) XLOOKUP settings to simulate exact return results as XLOOKUP, but is very slow
20. (21:31) Timing results of LOOKUP and XLOOKUP
21. (21:58) XLOOKUP and LOOKUP function algorithms to see how the binary options in each can yield different answers
22. (22:34) XLOOKUP can perform Vertical or Horizontal Lookup
23. (23:40) XLOOKUP with Exact Match or Next Bigger to lookup pipe size for land drainage pipes
24. (24:50) Custom Number Formatting to show numbers in inches
25. (26:50) Wildcard lookup with XLOOKUP to do a partial text lookup
26. (29:19) First-To-Last and Last-To-First search_mode options
27. (29:39) First-To-Last and Last-To-First search yield same results when there are no duplicates in the lookup_array
28. (30:24) What happens when there are duplicates in the lookup_array?
29. (31:27) Lookup First-To-Last when there are duplicate values in lookup_array. See example of looking up employees first day of work.
30. (32:35) Lookup Last-To-First when there are duplicate values in lookup_array. See example of looking up employees last sales amount.
31. (33:20) XLOOKUP to use two lookup values to lookup a City and Product Price
32. (34:43) Two-Way lookup with the XLOOKUP function. Example of getting a tax rate for employee with weekly wage and number of allowances
33. (38:07) FILTER function to return multiple items from one lookup value.
34. (40:33) XMATCH function to compare two lists
35. (44:08) SWITCH function to lookup three different lookup tables inside the LOOKUP function
36. (28:14) Homework – Practice Problems for you!
37. (48:30) Summary
38. (28:57) Closing