TEXTJOIN Formula in Excel: Solve Complex Lookup Problems with TextJoin
August 31, 2017 Excel Lookup Formulas
Download the workbook here: http://www.xelplus.com/complex-lookup-index-sumproduct/
Watch this video to find out the method used by Bill Szysz to get multiple lookup results in Excel. This example builds on the example shown in the previous videos 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.
Excel's TEXTJOIN function is a great solution to this problem. On top, when combined with the Find & Isnumber function, it can help us avoid duplicate results. Watch this video as I take you through Bill's formula solution.
✔ Alternate Solutions ✔
My version with Index & Sumproduct: https://rumble.com/v2tnep2
My with Index & Aggregate: https://rumble.com/v2tneqm
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
★ 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
-
3:22
One Bite Pizza Reviews
2 days agoBarstool Pizza Review - Marco Pizzeria & Restaurant (Branford, CT)
29.3K22 -
41:46
Standpoint with Gabe Groisman
12 hours agoEp. 27. From GameStop to the NBA. Gabe Plotkin
33.5K3 -
55:04
Matt Kohrs
9 hours agoThe Volatile Week Ahead || The MK Show
51.4K21 -
26:18
Stephen Gardner
8 hours agoTop Republican UNLOADS on Democrats and Biden's MENTAL HEALTH!!
64.8K100 -
44:30
Michael Franzese
2 days agoCancel Culture Backlash of My UK Tour | Michael Franzese
114K46 -
35:12
Island Hopper TV
1 day agoFull Costa Rica Travel Guide 2024
76.4K33 -
4:43
ParisDemers
1 day agoThis Diet Hack Changed My Life! (How To Get Ripped FAST!)
86K30 -
46:57
Crime Circus
1 day agoApple River ST*BBING!! Stand Your Ground Interrogation of Man in Wisconsin
89.7K46 -
9:39
Tactical Advisor
1 day agoHow To Improve Your Shooting For FREE! Mantis Blackbeard X
100K15 -
1:00:59
Squaring The Circle w/ Randall Carlson
1 day ago#007 The Real Climate Crisis Noone Is Talking About and Mount Tambora Pt 2
101K27