Excel Formula Lookup Multiple Results: Return headers based on matrix values
Download the workbook here: http://www.xelplus.com/complex-lookup-index-sumproduct/
Watch this video to find out my method of getting multiple lookup results in Excel. This example builds on the example shown in the previous video which has a matrix of apps which belong to different divisions. The apps are the values inside the matrix and the divisions are the headers. The aim is to find the header value based on the matrix value. Since the data in this example is not unique and an app can belong to more than one division, the aim is to come up with a formula that finds all the divisions one app could belong to.
✔ Alternate Solutions ✔
Watch Kevin's video to see 4 different approaches of solving this
2 with array formulas, 1 with helper columns and last one with a pivot table to normalize the date set: https://youtu.be/u4IcqcQP1FI
Watch Oz's Video from Excel on Fire here which shows how to solve this with a COUNTIFS function and the Power Query method: https://youtu.be/IwBYEXaOSOk
Another option that was suggested by Bill Szysz is: =TEXTJOIN(",", ,IF(H5=$B$5:$D$45,$B$4:$D$4,""))
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
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
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 Formula Lookup Multiple Results: Return headers based on matrix values канала Leila Gharani
Watch this video to find out my method of getting multiple lookup results in Excel. This example builds on the example shown in the previous video which has a matrix of apps which belong to different divisions. The apps are the values inside the matrix and the divisions are the headers. The aim is to find the header value based on the matrix value. Since the data in this example is not unique and an app can belong to more than one division, the aim is to come up with a formula that finds all the divisions one app could belong to.
✔ Alternate Solutions ✔
Watch Kevin's video to see 4 different approaches of solving this
2 with array formulas, 1 with helper columns and last one with a pivot table to normalize the date set: https://youtu.be/u4IcqcQP1FI
Watch Oz's Video from Excel on Fire here which shows how to solve this with a COUNTIFS function and the Power Query method: https://youtu.be/IwBYEXaOSOk
Another option that was suggested by Bill Szysz is: =TEXTJOIN(",", ,IF(H5=$B$5:$D$45,$B$4:$D$4,""))
★ My Online Excel Courses ► https://courses.xelplus.com/
✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/
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
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 Formula Lookup Multiple Results: Return headers based on matrix values канала Leila Gharani
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
![Excel DGET Function Solves 2 of Your VLOOKUP Problems](https://i.ytimg.com/vi/57MARBvYVs4/default.jpg)
![Excel Reverse Lookup Problem: Find Column Header based on Value in Matrix and Row Header](https://i.ytimg.com/vi/rqvsOoLoxck/default.jpg)
![Lookup and Find the 2nd, 3rd, or the Nth Matching Value in Excel](https://i.ytimg.com/vi/0PC5ydyQJeo/default.jpg)
![Excel 2019: Mit SUCHEN und LÄNGE Komponenten aus unterschiedlich langen Texten extrahieren](https://i.ytimg.com/vi/j-frqtbB_js/default.jpg)
![Find Multiple Match Results in Excel (Easier Solution For ALL Excel versions)](https://i.ytimg.com/vi/5cBUIa31AiA/default.jpg)
![](https://i.ytimg.com/vi/GxyZKHr86oQ/default.jpg)
![3 EASY Ways to Find and Remove Duplicates in Excel](https://i.ytimg.com/vi/ADArCWLz55Y/default.jpg)
![Aggregate Function in Excel in hindi | Sum a Range Ignoring Error Value | Advanced Excel](https://i.ytimg.com/vi/bF8cqMK4jBY/default.jpg)
![Advanced Pivot Table Techniques: Combine Data from Multiple Sheets in Excel](https://i.ytimg.com/vi/mTdIEhtcqlo/default.jpg)
![Excel Complex Lookup: Find Header based on Lookup Criteria in Matrix with INDEX & SUMPRODUCT](https://i.ytimg.com/vi/OJLfPc9YlqE/default.jpg)
![How to use Excel Index Match (the right way)](https://i.ytimg.com/vi/F264FpBDX28/default.jpg)
![Excel 2010 Magic Trick 671: AGGREGATE function Array Formula #5 Extract Records 2 criteria](https://i.ytimg.com/vi/KPhvLMzXa_8/default.jpg)
![Excel IF Formula: Simple to Advanced (multiple criteria, nested IF, AND, OR functions)](https://i.ytimg.com/vi/KkTaQ5OjAGc/default.jpg)
![Lookup values across multiple worksheets: VLOOKUP / INDEX MATCH in Excel](https://i.ytimg.com/vi/2erErC7LvPY/default.jpg)
![Formula to Get the Last Value from a List in Excel](https://i.ytimg.com/vi/RaHrGmGPcPw/default.jpg)
![Return Multiple Match Results in Excel (2 methods)](https://i.ytimg.com/vi/fDB1Ktyhp3Y/default.jpg)
![Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)](https://i.ytimg.com/vi/7mo4COng7Sg/default.jpg)
![Advanced Excel Index Match (3 Most Effective Formulas for Multiple Criteria)](https://i.ytimg.com/vi/ontXHp9cwOQ/default.jpg)
![How to Combine and Repeat Blocks of Text n Times in Excel (NO Copy Paste and NO Formulas)](https://i.ytimg.com/vi/9HpAWDi2y_E/default.jpg)
![INDEX MATCH Excel Tutorial](https://i.ytimg.com/vi/6JhbY8Mku1A/default.jpg)