Excel XLOOKUP Function – Comprehensive Lessons – 27 Examples - Excel Magic Trick 1600
Download Excel Start File (to follow along with video): https://excelisfun.net/files/EMT1600.xlsx
Download Excel File with All Examples Completed AFTER video is don: https://excelisfun.net/files/EMT1600Finished.xlsx
Learn about the new Office 365 XLOOKUP Function. This functions replaces much of what we did with historically earlier functions: VLOOKUP, HLOOKUP, LOOKUP, INDEX and MATCH.
Topics in video:
(00:12) Introduction
1. (02:00) Exact Match is XLOOKUP Default
2. (02:52) 3 arguments for Exact Match, one fewer than VLOOKUP
3. (03:37) Can insert Columns and it still works!!!!!
4. (04:38) Approximate Match using Exact Match , previous smallest, match_mode argument set to -1. See example of LOOKUP function (old function) that may be more efficient than newer XLOOKUP.
5. (07:29) Approximate Match & You Don't Have To Sort!!!!!
6. (07:56) Approximate Match using Exact Match , previous biggest, match_mode argument set to 1
7. (09:03) Find Last Values, Amongst Duplicates. (10:16) Also see XLOOKUP as Spilled Array. Also see SORT & UNIQUE Functions.
8. (11:15) Find First Values, Amongst Duplicates
9. (11:45) Lookup Left. (11:31) Important Lesson about Dimensions of lookup_array and return_array.
10. (12:52) Horizonal or Vertical Lookup, or Both. More important lessons about dimension sizes of lookup_array and return_array.
11. (14:02) Two Way Lookup. First look at “lookup a range”. More important lessons about dimension sizes of lookup_array and return_array. (15:58) See example of VLOOKUP & MATCH functions (old functions) that may be more efficient than newer XLOOKUP.
12. (16:37) Lookup Row
13. (17:24) Lookup Column
14. (17:38) Return Multiple Items.
15. (17:38) Can Retrieve whole record.
16. (18:37) But these methods require a static order and not dependent on Field Names
17. (18:44) Return Multiple Items with Dynamic Columns. Including Spilled Arrays.
18. (18:44) These methods are Dynamic! These methods lookup items based on Field Names.
19. (18:44) Old Methods for returning Records with columns specified in certain Order.
20. (19:16) New Methods for returning Records with columns specified in certain Order.
21. (20:16) INDEX & XMATCH. And (20:57) VLOOKUP and XMATCH. Old and New Together for returning Records with columns specified in certain Order.
22. (21:50) Rearrange Columns
23. (22:16) Lookup Cell References
24. (22:47) Lookup Picture
25. (23:00) Lookup Table. XLOOKUP is not a good method : (
26. (23:40) Wildcards
27. (24:14) Array Formulas
(24:35) Summary
Other Video Topics:
XLOOKUP in Excel is VLOOKUP Slayer, VLOOKUP... Gone But Not Forgotten
XLOOKUP or INDEX-MATCH-MATCH Head-to-Head
Microsoft introduces XLOOKUP in Excel – and it's a big deal
The New XLOOKUP: Compared to VLOOKUP & INDEX,
The New XLOOKUP Function for Excel
VLookup Fired...XLookup Hired - The New Giant Is Here
Видео Excel XLOOKUP Function – Comprehensive Lessons – 27 Examples - Excel Magic Trick 1600 канала ExcelIsFun
Download Excel File with All Examples Completed AFTER video is don: https://excelisfun.net/files/EMT1600Finished.xlsx
Learn about the new Office 365 XLOOKUP Function. This functions replaces much of what we did with historically earlier functions: VLOOKUP, HLOOKUP, LOOKUP, INDEX and MATCH.
Topics in video:
(00:12) Introduction
1. (02:00) Exact Match is XLOOKUP Default
2. (02:52) 3 arguments for Exact Match, one fewer than VLOOKUP
3. (03:37) Can insert Columns and it still works!!!!!
4. (04:38) Approximate Match using Exact Match , previous smallest, match_mode argument set to -1. See example of LOOKUP function (old function) that may be more efficient than newer XLOOKUP.
5. (07:29) Approximate Match & You Don't Have To Sort!!!!!
6. (07:56) Approximate Match using Exact Match , previous biggest, match_mode argument set to 1
7. (09:03) Find Last Values, Amongst Duplicates. (10:16) Also see XLOOKUP as Spilled Array. Also see SORT & UNIQUE Functions.
8. (11:15) Find First Values, Amongst Duplicates
9. (11:45) Lookup Left. (11:31) Important Lesson about Dimensions of lookup_array and return_array.
10. (12:52) Horizonal or Vertical Lookup, or Both. More important lessons about dimension sizes of lookup_array and return_array.
11. (14:02) Two Way Lookup. First look at “lookup a range”. More important lessons about dimension sizes of lookup_array and return_array. (15:58) See example of VLOOKUP & MATCH functions (old functions) that may be more efficient than newer XLOOKUP.
12. (16:37) Lookup Row
13. (17:24) Lookup Column
14. (17:38) Return Multiple Items.
15. (17:38) Can Retrieve whole record.
16. (18:37) But these methods require a static order and not dependent on Field Names
17. (18:44) Return Multiple Items with Dynamic Columns. Including Spilled Arrays.
18. (18:44) These methods are Dynamic! These methods lookup items based on Field Names.
19. (18:44) Old Methods for returning Records with columns specified in certain Order.
20. (19:16) New Methods for returning Records with columns specified in certain Order.
21. (20:16) INDEX & XMATCH. And (20:57) VLOOKUP and XMATCH. Old and New Together for returning Records with columns specified in certain Order.
22. (21:50) Rearrange Columns
23. (22:16) Lookup Cell References
24. (22:47) Lookup Picture
25. (23:00) Lookup Table. XLOOKUP is not a good method : (
26. (23:40) Wildcards
27. (24:14) Array Formulas
(24:35) Summary
Other Video Topics:
XLOOKUP in Excel is VLOOKUP Slayer, VLOOKUP... Gone But Not Forgotten
XLOOKUP or INDEX-MATCH-MATCH Head-to-Head
Microsoft introduces XLOOKUP in Excel – and it's a big deal
The New XLOOKUP: Compared to VLOOKUP & INDEX,
The New XLOOKUP Function for Excel
VLookup Fired...XLookup Hired - The New Giant Is Here
Видео Excel XLOOKUP Function – Comprehensive Lessons – 27 Examples - Excel Magic Trick 1600 канала ExcelIsFun
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
Excel XLOOKUP Function Definitive GuideMysteries of VLOOKUP Function Revealed! 15 Amazing Examples! (Excel Magic Trick 1514)Excel Pivot Tables EXPLAINED in 10 Minutes (Productivity tips included!)Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data ModelComprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)Searchable Drop Down List in Excel (Very Easy with FILTER Function)Single Cell Spilled Reports with Dynamic Total Row & Formatting. Excel Magic Trick 1703.50 Ultimate Excel Tips and Tricks for 2020The New XLOOKUP Function: Compared to VLOOKUP & INDEX MATCH in Excel☑️ Top 30 Advanced Excel Tips and TricksINDEX & MATCH Excel Lookup Functions - All You Need To Know in 10 Minutes (EMT 1618)10 awesome things you can do in Excel - WITHOUT any formulas or pivotsXLOOKUP Excel Tutorial10 Excel XLOOKUP Function Examples (Better than VLOOKUP & INDEX/MATCH)Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)Excel Partial Text Lookup Formulas. Top 5 methods for Fuzzy Lookup. Excel Magic Trick 1691.Basics of Excel Spilled Array Formulas to create Monthly Sales Report. Excel Magic Trick 1702.How to easily automate boring Excel tasks with Power Query!☑️ Top 25 Advanced Pivot Table Tips & Tricks For Microsoft Excel