Excel XLOOKUP Function – Comprehensive Lessons – 27 Examples - Excel Magic Trick 1600

Video Description

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