INDEX & MATCH or XLOOKUP for Lookup Formula in SUMIFS Criteria Argument. Excel Magic Trick 1668.
28.1 هزار بار بازدید -
4 سال پیش
-
https://excelisfun.net/files/EMT1668....
Learn how to lookup up Book ID given a book title, inside the criteria argument of the SUMIFS Function, with the goal of add total units sold for each book. See traditional formula using INDEX, MATCH & SUMIFS. See new Microsoft 365 Spilled Array Formula using XLOOKUP and SUMIFS.
Fundamental Reporting Problem illustrated in Video: We need totals for Book Title, but Data Set Has Book ID for each record, not book title.
Topics:
1. (00:01) Introduction
2. (00:37) INDEX and MATCH Function to “Lookup an Item to the Left”. Goal: Lookup Book ID, so we can use it inside SUMIFS. This is a Traditional Formula.
3. (01:41) SUMIFS to add units for each book ID.
4. (02:12) What we are required to do with a Traditional Excel Formula.
5. (02:20) XLOOKUP to lookup Book ID and Spill all the Book IDs. This Spilled Array Formula does not require that we lock cell references or Copy the Formula.
6. (03:46) Why Spilled Array Formula are MUCH Easier than Traditional Formulas.
7. (03:56) SUMIFS with XLOOKUP to add units for each book ID.
8. (04:28) Summary of Video and End Video Links
Learn how to lookup up Book ID given a book title, inside the criteria argument of the SUMIFS Function, with the goal of add total units sold for each book. See traditional formula using INDEX, MATCH & SUMIFS. See new Microsoft 365 Spilled Array Formula using XLOOKUP and SUMIFS.
Fundamental Reporting Problem illustrated in Video: We need totals for Book Title, but Data Set Has Book ID for each record, not book title.
Topics:
1. (00:01) Introduction
2. (00:37) INDEX and MATCH Function to “Lookup an Item to the Left”. Goal: Lookup Book ID, so we can use it inside SUMIFS. This is a Traditional Formula.
3. (01:41) SUMIFS to add units for each book ID.
4. (02:12) What we are required to do with a Traditional Excel Formula.
5. (02:20) XLOOKUP to lookup Book ID and Spill all the Book IDs. This Spilled Array Formula does not require that we lock cell references or Copy the Formula.
6. (03:46) Why Spilled Array Formula are MUCH Easier than Traditional Formulas.
7. (03:56) SUMIFS with XLOOKUP to add units for each book ID.
8. (04:28) Summary of Video and End Video Links
4 سال پیش
در تاریخ 1399/02/24 منتشر شده
است.
28,105
بـار بازدید شده