Join 400,000+ professionals in our courses here https://link.xelplus.com/yt-d-all-courses

Watch this video to find out my method of getting multiple lookup results in Excel.

⬇️ Download the workbook here: https://pages.xelplus.com/sumproduct-index-file

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.

Key Highlights:
- Unique vs. Non-Unique Datasets: Differentiating between unique and non-unique datasets in Excel.
- Problem Identification: Discovering issues when using SUMPRODUCT in non-unique datasets.
- Solution Strategy: Employing the LARGE function for extracting values and the AGGREGATE function for handling arrays.
- Practical Application: Real-world example with a modified dataset, demonstrating the process from start to finish.
- Error Handling: Tips for using IFERROR and IF functions for efficient formula execution.

My Online Excel Courses ► https://www.xelplus.com/courses/

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,""))

️ Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join

Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/

Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/

RESOURCES I recommend: https://www.xelplus.com/resources/

Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
LinkedIn: https://www.linkedin.com/company/xelplus

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!

#excel