Download Excel Start File (to follow along with video): [ Ссылка ]
Download Excel File with All Examples Completed AFTER video is don: [ Ссылка ]
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
