Lookup 1 Value, Return Many Matches
The VLOOKUP and INDEX & MATCH formulas are great for looking up a value in a large data table and returning a result from the adjacent columns.
But how can you return multiple results?
What if your lookup value isn’t unique? What if it’s repeated in your data set?
The standard formulas always return the first match.
You’d like to have a list of all the matches and you’d like to have it in a dynamic way.
In the video below I show you 2 different methods that return multiple matches:
Method 1 uses INDEX & AGGREGATE functions. It’s a bit more complex to setup, but I explain all the steps in detail in the video. It’s an array formula but it doesn’t require CSE (control + shift + enter).
Method 2 uses the TEXTJOIN function. This is a relatively new function in Excel 2016, Office 365. It’s quite simple to setup but it’s an array formula that requires CSE.
Watch the steps in this video:
Feel free to Download the Workbook HERE.
The new Excel Dashboards course is here!
Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.