## Excel Magic Trick 1481: Multiple Table Lookup: SWITCH or INDIRECT Function? VLOOKUP too

About this tutorial: Video duration: 11:39 Download Files: Start Excel File: Entire page with all Excel Files for All Videos: about how to In this video learn how to how to perform lookup from multiple tables. Learn two methods: 1) VLOOKUP and SWITCH Function or 2) VLOOKUP and INDIRECT Function with Defined Names. 1) (00:01) […]

## Excel Magic Trick 1539: Extract Daily Sales Records With Formula. FILTER Function (Dynamic Array).

About this tutorial: Video duration: 5:46 Download Excel Start Files: Entire page with all Excel Files for All Videos: In this video learn how to extract sales records based by day with a formula. Learn about the New Office 365 Dynamic Array Functions: FILTER, SORT and UNIQUE. Compare and contrast this new method to the […]

## Excel Magic Trick 1504: Lookup Cell Address: 4 Examples: MATCH, ADDRESS, CELL function?

About this tutorial: Video duration: 6:23 Download Excel File: Entire page with all Excel Files for All Videos: In this video see 4 different formulas to lookup a cell reference. Topics: 1. (00:06) Introduction 2. (00:33) Formula #1: MATCH Function 3. (01:55) Formula #2: ADDRESS Function with MATCH & COLUMN Functions 4. (03:08) Formula #3: […]

## Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic Trick 1569

About this tutorial: Video duration: 2:40 Download Excel File: In this video learn about how to use the Union Operator in standard Aggregate functions, like SUM and AVERAGE and how to use it inside of parentheses inside Statistical and Finance Functions. Related Videos: Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic […]

## Excel Magic Trick 1513: COUNTIFS from Multiple Cells!?!? Array Formula or Logical Formula?

About this tutorial: Video duration: 5:26 Download Excel File: Entire page with all Excel Files for All Videos: In this video see how to count values that are greater than a hurdle when the values in in noncontiguous cells (cells not next to each other). See an Array Formula that uses SUMPRODUCT and CHOOSE and […]

## Dynamic Array & Old Array Formulas to Create Student Classes Report – Excel Magic Trick 1608

About this tutorial: Video duration: 7:48 Download Excel File: Learn how to create a report that shows each student by quarter with a list of classes in a single cell. See many of the new Excel 365 Functions like: SORTBY, UNIQUE, FILTER, TEXTJOIN and the ROWS Function. See how we can combine Spilled Arrays formulas […]

## Excel Magic Trick 1279: New Running Count Formula After Each Zero Value (Cumulative Count)

About this tutorial: Video duration: 2:15 Download File: Learn how to create a New Running Count (Cumulative Count) Formula After Each Zero Value using the IF and SUM functions. Trick from Bill Szysz at YouTube. See that the SUM Function ignores Text Values.

## Excel Magic Trick 1509: Conditional Format Array Formula to Highlight Row With 2 Lookup Values

About this tutorial: Video duration: 9:49 Download Files: Entire page with all Excel Files for All Videos: In this video see how to color a row with conditional formatting using an Array Formula & the MATCH Function that will lookup Two Lookup Values in a corresponding Table.

## DAX Measure & Data Model PivotTable – List Student Classes in Single Cell –- Excel Magic Trick 1607

About this tutorial: Video duration: 4:22 Download Excel Start File: Download Excel Finished File: Learn how to create a report that shows each student by quarter with a list of classes in a single cell. Learn how to create a Data Model PivotTable and a DAX Measure that uses the DAX Function CONCATENATEX using the […]

## Excel Magic Trick 1501: INDIRECT Function: Pull Data from Different Sheet Based on Row Number

About this tutorial: Video duration: 5:49 Download Excel File: Entire page with all Excel Files for All Videos: In this video learn how to use the INDIRECT Function to pull Data from a Different Sheet Based on Row Number.

## Excel Magic Trick 1435: DAX Query to Export Data from Data Model to Excel Sheet & Much More!

About this tutorial: Video duration: 17:49 Download File: Start File: Finished File: Entire page with all Excel Files for All Videos: See how to query the Data Model with DAX Code to Export Data to an Excel Sheet: 1. (00:14) Introduction, including looking at Data Model that downloadable file contains 2. (02:13) Use Existing Connections […]

## Excel Magic Trick 1457 Part 2: Regional Settings & Text or Number Date / Times in SUMIFS Function

About this tutorial: Video duration: 3:21 Download File: Entire page with all Excel Files for All Videos: In the English United States Regional Settings, SUMIFS seems to be recognizing Text and Number Dates as equivalent Criteria. But when we change the Reginal Settings, SUMIFS correctly interprets Text Dates and Number Dates as different. This Tip […]

## Power Query – Filter Table Based on Distinct NOT Conditions from List – Excel Magic Trick 1592

About this tutorial: Video duration: 9:18 Download Excel File: Learn how to filter imported data using Distinct NOT Conditions from List. See a single M Code Power Query Formula using the functions. Table.SelectRows, Text.Split, List.Intersect and List.IsEmpty. This amazing solution comes from Bill Szysz.

## Power Query Running Count Based on Row Condition – Excel Magic Trick 1588

About this tutorial: Video duration: 7:25 COUNTIFS(\$A\$1:A1,A1) Formula in Power Query? – Excel Magic Trick 1588 Download Excel File: Learn how to create a Power Query Custom Column Formula that has an expandable range, counts based on the row condition and is equivalent to the Excel Worksheet Formula COUNTIFS(\$A\$1:A1,A1). See how to use the Power […]

## Excel Two Way Lookup with VLOOKUP & MATCH Functions – Excel Magic Trick 1567

About this tutorial: Video duration: 2:40 Download Excel File: In this video learn how to do a Two Way Lookup from a Rectangular Lookup Table using the VLOOKUP and MATCH functions. In our example we need to lookup a product name in the row and a month name in the column and return the intersecting […]