excelisfun | All Important Excel Lookup Formulas: Excel Worksheet, Power Query & DAX 28 Examples! - 365 MECS 08 @excelisfun | Uploaded 1 year ago | Updated 1 day ago
Download Excel file: https://excelisfun.net/files/08-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/08-M365ExcelClass.pdf
Power Query Test Append and Merge Aprox. Match Lookup Methods: https://excelisfun.net/files/TestAproxMatchAppendAndMergePQ.xlsx
This video is a comprehensive lesson in lookup formulas in the Excel worksheet, Power Query and DAX formulas.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
Topics in video:
1. (00:00) Introduction
2. (01:35) XLOOKUP function
3. (02:04) Exact Match Lookup
4. (04:00) Data Validation List
5. (07:19) Lookup a Record
6. (08:50) Lookup a Column
7. (10:37) Exact Match or Next Smaller Lookup (Approximate Match Lookup)
8. (14:24) Vertical or Horizontal Lookup?
9. (15:40) Lower Limit for Exact Match or Next Smaller Lookup
10. (16:48) Exact Match or Next Bigger Lookup
11. (21:15) Wild Card Lookup
12. (23:10) Lookup First Item
13. (24:57) Lookup Last Item
14. (26:11) Lookup last sale made with MAXIFS function
15. (29:06) Two-Way Lookup
16. (31:27) Two Lookup Values
17. (33:36) Lookup a Range of Values with a Specified Start and End Value
18. (37:39) FILTER function
19. (38:00) Single Lookup Value, Return Multiple Matches
20. (39:18) XMATCH function
21. (39:39) Compare Two Lists
22. (43:15) INDEX function
23. (43:39) Random Lookup: single value and a complete column
24. (48:07) LOOKUP function
25. (49:07) Vertical and Horizontal Lookup?
26. (49:50) Tax Example where LOOKUP beats XLOOKUP
27. (54:00) SWITCH function to Lookup a Table
28. (01:00:55) XLOOKUP Spilled Exact Match Lookup
29. (01:01:21) XLOOKUP Table Exact Match Lookup
30. (01:02:50) Power Query: Exact Match Lookup
31. (01:06:17) Data Model DAX: Exact Match Lookup
32. (01:07:33) Concept for Approximate Match Lookup in DAX & M Code formulas
33. (01:10:13) Data Model DAX: Approximate Match Lookup
34. (01:13:09) Power Query: Approximate Match Lookup
35. (01:18:05) Summary and Conclusions
36. (01:18:28) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/ . Artist: http://audionautix.com/
#MikeGirvin
#excelisfun
#MikeexcelisfunGirvin
#Microsoft365Excel
#MECS08
#Microsoft365ExcelCompleteStory
#ExcelLookupFormulas
#VLOOKUPfunction
#XLOOKUPfunction
#INDEXfunction
#Matchfunction
#LOOKUPfunction
#SWITCHfunction
#Howtolookuprecord.MultipleLookupTables?
#PowerQueryApproximateMatchLookup
#DAXApproximateMatchLookup
#MostImportantExcelLookupFunctions
#FunctionsandFormulas
#LOOKUPfunctionbeatsXLOOKUP
#FreeClass
#FreePowerBIClass
#xlookup
#excelformulas
#excelfunctions
#vlookup
#dax
#mcode
#powerbi
#powerquery
Download Excel file: https://excelisfun.net/files/08-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/08-M365ExcelClass.pdf
Power Query Test Append and Merge Aprox. Match Lookup Methods: https://excelisfun.net/files/TestAproxMatchAppendAndMergePQ.xlsx
This video is a comprehensive lesson in lookup formulas in the Excel worksheet, Power Query and DAX formulas.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
Topics in video:
1. (00:00) Introduction
2. (01:35) XLOOKUP function
3. (02:04) Exact Match Lookup
4. (04:00) Data Validation List
5. (07:19) Lookup a Record
6. (08:50) Lookup a Column
7. (10:37) Exact Match or Next Smaller Lookup (Approximate Match Lookup)
8. (14:24) Vertical or Horizontal Lookup?
9. (15:40) Lower Limit for Exact Match or Next Smaller Lookup
10. (16:48) Exact Match or Next Bigger Lookup
11. (21:15) Wild Card Lookup
12. (23:10) Lookup First Item
13. (24:57) Lookup Last Item
14. (26:11) Lookup last sale made with MAXIFS function
15. (29:06) Two-Way Lookup
16. (31:27) Two Lookup Values
17. (33:36) Lookup a Range of Values with a Specified Start and End Value
18. (37:39) FILTER function
19. (38:00) Single Lookup Value, Return Multiple Matches
20. (39:18) XMATCH function
21. (39:39) Compare Two Lists
22. (43:15) INDEX function
23. (43:39) Random Lookup: single value and a complete column
24. (48:07) LOOKUP function
25. (49:07) Vertical and Horizontal Lookup?
26. (49:50) Tax Example where LOOKUP beats XLOOKUP
27. (54:00) SWITCH function to Lookup a Table
28. (01:00:55) XLOOKUP Spilled Exact Match Lookup
29. (01:01:21) XLOOKUP Table Exact Match Lookup
30. (01:02:50) Power Query: Exact Match Lookup
31. (01:06:17) Data Model DAX: Exact Match Lookup
32. (01:07:33) Concept for Approximate Match Lookup in DAX & M Code formulas
33. (01:10:13) Data Model DAX: Approximate Match Lookup
34. (01:13:09) Power Query: Approximate Match Lookup
35. (01:18:05) Summary and Conclusions
36. (01:18:28) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/ . Artist: http://audionautix.com/
#MikeGirvin
#excelisfun
#MikeexcelisfunGirvin
#Microsoft365Excel
#MECS08
#Microsoft365ExcelCompleteStory
#ExcelLookupFormulas
#VLOOKUPfunction
#XLOOKUPfunction
#INDEXfunction
#Matchfunction
#LOOKUPfunction
#SWITCHfunction
#Howtolookuprecord.MultipleLookupTables?
#PowerQueryApproximateMatchLookup
#DAXApproximateMatchLookup
#MostImportantExcelLookupFunctions
#FunctionsandFormulas
#LOOKUPfunctionbeatsXLOOKUP
#FreeClass
#FreePowerBIClass
#xlookup
#excelformulas
#excelfunctions
#vlookup
#dax
#mcode
#powerbi
#powerquery
