Excel Lookup to Return Multiple Values with FILTER Function
Check out the complete Excel Course covering the brand new functions: https://courses.xelplus.com/p/excel-dynamic-array-formulas
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 Office 365 - also for Excel on the Web and Mobile.
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: https://www.xelplus.com/excel-filter-function
Return Multiple Match Results (Excel Legacy Method): https://youtu.be/fDB1Ktyhp3Y
More about Excel Dynamic Arrays: https://youtu.be/2USJsIyIzvo
Excel Dynamic Arrays Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyPFY31LldHWcJdLzGUPTSP
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Take this Quiz to choose the right Excel course: https://www.xelplus.com/course-quiz/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Camera: https://amzn.to/2FLiFho
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Microphone: https://amzn.to/2DVKstA
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
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
Видео Excel Lookup to Return Multiple Values with FILTER Function канала Leila Gharani
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 Office 365 - also for Excel on the Web and Mobile.
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: https://www.xelplus.com/excel-filter-function
Return Multiple Match Results (Excel Legacy Method): https://youtu.be/fDB1Ktyhp3Y
More about Excel Dynamic Arrays: https://youtu.be/2USJsIyIzvo
Excel Dynamic Arrays Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyPFY31LldHWcJdLzGUPTSP
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Take this Quiz to choose the right Excel course: https://www.xelplus.com/course-quiz/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Camera: https://amzn.to/2FLiFho
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Microphone: https://amzn.to/2DVKstA
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
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
Видео Excel Lookup to Return Multiple Values with FILTER Function канала Leila Gharani
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
Excel FILTER Function TRICK for Non Adjacent ColumnsSURPRISING Advanced Filter TRICK in Excel (You've Never Heard Of!)How to Extract Data from a Spreadsheet using VLOOKUP, MATCH and INDEXDiscover What XLOOKUP Can Do For YOU (R.I.P. Excel VLOOKUP)Find Multiple Match Results in Excel (Easier Solution For ALL Excel versions)INDEX MATCH Explained (An Alternative to VLOOKUP)Excel Dynamic Arrays (How they will change EVERYTHING!)Return Multiple Match Results in Excel (2 methods)Excel Pivot Tables EXPLAINED in 10 Minutes (Productivity tips included!)Excel FILTER Function Explained (7 Examples) | Filter and Extract Data EasilyExcel shortcut keys you SHOULD know!Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)Excel FILTER Function - Is this the Best Excel Function?Excel OFFSET Function for Dynamic Calculations - Explained in Simple Steps10 Excel XLOOKUP Function Examples (Better than VLOOKUP & INDEX/MATCH)4 Hidden Excel Dashboard Design Tips for Beautiful ReportsSearchable Drop Down List in Excel (Very Easy with FILTER Function)MS Excel - Filtering DataHow Power Query Will Change the Way You Use Excel