Check out the complete Excel Dynamic Arrays Course: You'd like to do a VLOOKUP but return ALL match results instead of just the first match? You can use Excel's new FILTER function.
What if you'd like to lookup multiple criteria and return ALL match results? You can use the new Excel Dynamic Array FILTER Formula! It's like the dynamic or automatic version of Excel's advanced Filter feature. You can return Filtered results based on AND as well as OR conditions.
The Excel Filter function filters your data based on criteria, and returns all the matching records. This can be one or more records. If more than one match is found, the results will "spill" vertically. If there is a blockage (i.e. your results are spilling into already filled cells) then you will get the #SPILL error. You can easily get rid of this error by removing the cells that are blocking the FILTER formula.
If FILTER doesn't find any matching records it will return a #CALC error. You can suppress this error with the text or value of your choice by adding this to the last argument of the FILTER formula.
* Availability of Excel FILTER function: This feature is available on Excel for the Web and Mobile. It is currently being rolled out to Excel desktop users with Office 365 (Make sure you're set to receive the latest updates) - some of you already have it.
Quick Navigation:
1. Advantages of Excel Filter function: 0:45
2. Excel Filter Function Explained: 1:46
3. Excel FILTER #CALC Error: 3:15
4. Excel #SPILL Error and how to overcome it: 5:40
5. Excel FILTER Function with Table References: 6:13
6. Excel FILTER formula with multiple lookup criteria: 9:20
⯆ DOWNLOAD the workbook here:
Return Multiple Match Results (Excel Legacy Method):
More about Excel Dynamic Arrays:
Excel Dynamic Arrays Playlist:
★ My Online Excel Courses ►
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
EXCEL RESOURCES I Recommend:
Get Office 365:
Microsoft Surface:
GEAR
Camera:
Screen recorder:
Microphone:
Lights:
More resources on my Amazon page:
Let’s connect on social:
Instagram:
Twitter:
LinkedIn:
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel
0 Yorumlar