MrExcel Excel Videos

Here is a look at the final version of XLOOKUP, along with 10 benefits of XLOOKUP over VLOOKUP, HLOOKUP, LOOKUP, INDEX/MATCH. Table of Contents (0:00 Welcome and Introduction to Excel XLOOKUP Deep Dive (0:25) List of Benefits (2:30) First XLOOKUP (3:02) 3rd argument is not an integer (3:29) Handle items not found (3:51) Range lookup find value just less like VLOOKUP(,,,True) (4:41) Find value just larger like MATCH - without sorting (5:16) HLOOKUP replaced by XLOOKUP (5:30) Search from bottom of table (6:21) Wildcards in XLOOKUP versus VLOOKUP (6:56) Return all 12 months from table with single formula (7:38) XLOOKUP can return a cell reference XLOOKUP:XLOOKUP (9:10) Two-Way Match (10:09) Sum All lookups (10:44) Twisted XLOOKUP (11:13)...

Today, November 3, 2019, the function arguments for XLOOKUP changed. The if_not_found argument moved from 6th to 5th. That means any old XLOOKUP functions that used arguments 4, 5, or 6 might break at the next Recalc.

This is a recording of the October 2019 monthly webinar in the Excel MVP Webinar series. In this free 59-minute webinar, Bill Jelen covers many pivot table techniques: Table of Contents (0:00) Welcome to the Pivot Table Webinar (1:36) Traditional Pivot Cache Pivot Tables (2:45) Setting up data for a successful pivot table (3:40) Using Alt+Enter for headings (4:00) Avoiding tiny blank columns between columns (4:15) Accounting Style Underlines (5:35) Pivot Table 101 – Your First Pivot Table (7:55) Changing Number Format in Pivot Tables (8:35) Moving fields around in a pivot table (9:40) Drill-down on any pivot table number (10:45) Refreshing pivot table if the underlying data changes (11:25) Year-over-Year Pivot Tables – Grouping Dates...

Vote for the improvement to Filtering: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/31201495-filtering While thinking about the Filter problem, I decided to check to see if the data profile improvements had been added to Power Query in Excel, and they are! You can now see how many distinct and unique items there are in each column.

The new XLOOKUP for Excel debuted yesterday. Can it replace INDEX-MATCH? Can it replace INDEX-MATCH-MATCH? The answer is Yes!

You have a bunch of zip codes (postal codes) in Excel. What can you do with those? Thanks to the new Office 365 Geography Data type, you can easily get the city, county, state or province from Excel. Then, I show you how to use a State slicer to create a zip code map for any state showing where your customers are located.

Robert asks: Is there a fast way to take a 60-sheet Excel file and create a series of linked tables in Word? Today, using some code from Suat Ozgur, a macro to solve the problem. Buy Suat's Javascript Book: Excel JavaScript UDFs Straight to the Point Download the Macro: https://www.mrexcel.com/download-center/2019/06/macrototransfertoword.xlsm Videos from MBAS:

If you work in Accounts Payable, check out the great resources at AP-Now.com. Today's question from a recent webinar that I did for the AP-Now members: Given an Excel worksheet of payments made by accounts payable, how can you look for duplicate payments. My solution involves the new TEXTJOIN function, the Mark Duplicates feature in Conditional Formatting, and then Filter by Color.

Kim has a distribution list in Outlook with 130+ names. She wants those names in Excel. When she copies and pastes, everything ends up in a single cell. In this episode, a simple set of steps to split the distribution list into rows and then columns for Name and E-Mail address. These steps won't (yet) work on a Mac. You are using the Get & Transform tools (also known as Power Query) on the Windows version of Excel.