Скачать или смотреть Excel - Array Formula to Extract Records - Episode 1746

Microsoft Excel Tutorial: Extracting Records with a Formula in Excel | MrExcel Podcast.

Welcome to another episode of the MrExcel podcast! In today's episode, we will be discussing how to extract records with a formula in Excel. This question was sent in by Nermal, who has a large data set with multiple criteria fields and wanted a formula that would automatically extract matching records when the criteria changed. Let's dive into the solution!

At first, I thought this would be an easy task using the Advanced filter. However, I soon realized that it was not feasible to expect the user to go through all the steps of the Advanced filter every time the criteria changed. That's when I turned to Mike Girvin's book, "The Ctrl+Shift+Enter" ePub, which is now available on mrexcel.com. Mike is an expert in array formulas, and his book has a whole chapter dedicated to extracting records. So, I decided to use his method to build a formula that would solve Nermal's problem.

The main component of this formula is the INDEX function, which pulls out a specific record number from the original data set. But the real magic lies in the array formula that determines the record numbers. This is where we use the IF and ROW functions to check if the criteria match and return the row number of the matching record. We then use the SMALL function to extract the smallest value from this array, which gives us the record number we need. By using the ROW function with a cell reference, we can easily copy the formula down to get the record numbers for all the matching records.

In the simple case of one criteria, we can use the AND function to check if all the criteria match. However, in cases with multiple criteria, we have to nest IF statements to check each criteria individually. This is because the AND function does not work with array formulas. But once we have the record numbers, we can use the INDEX function to pull out the corresponding data for those records. This formula may seem intimidating at first, but once you break it down, it's just a combination of common functions like IF, ROW, and SMALL.

Although this formula may be expensive in terms of calculation time, it saves the hassle of running the Advanced filter every time the criteria change. This is especially useful for large data sets like Nermal's. I highly recommend checking out Mike Girvin's book for more information on array formulas. It's a comprehensive resource that covers all the important aspects of array formulas and their applications. The eBook version is already available on mrexcel.com, and the print edition will be released soon.

I want to thank Nermal for sending in the question and for all of you for tuning in to this episode of the MrExcel podcast. I hope you found this solution helpful and will consider using it in your own projects. Don't forget to check out Mike Girvin's book for more insights on array formulas. Thank you for watching, and I'll see you in the next netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Table of Contents:
(00:00) Extracting Records with a Formula
(00:12) Criteria Fields and Need for a Formula
(00:22) Using Advanced Filter vs. Formula
(00:32) Introduction to the Formula
(01:00) Plug for Mike Girvin's Book on Array Formulas
(01:18) Explanation of the Formula
(02:05) Simplified Example of the Formula
(02:15) Explanation of the Magic Array Formula
(03:50) Use of IF and ROW Functions
(04:55) Use of SMALL Function
(05:30) Explanation of the Guts of the Formula
(06:33) Use of IFERROR and Extending the Formula
(07:08) Use of Nested IF Statements for Multiple Criteria
(07:44) Demonstration of the Formula in Action
(08:24) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorials

This video answers these common search terms:
Advanced filter
AND function with array formulas
Array formulas
Criteria fields
Data filter
Extract Records with a Formula
IF function
INDEX function
Mike Girvin's book
Nermal's question
SMALL function


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...


Nirml asks how to extract all records between certain dates for a customer, using a formula. This requires a fairly complex array formula. Using a page from Mike ExcelisFun Girvin's new book, I explain how to extract records using a formula.