Excel VLOOKUP Victory: Join All VLOOKUP Duel 183 - Episode 2091
2,803 views · Published 5 May 2017 · 11:04 · Indexed 29 October 2026
Channel: MrExcel.com · 2017 · Education
Microsoft Excel Tutorial: Concatenate all of the text answers from a VLOOKUP
Welcome back to another episode of Dueling Excel, where Mike Girvin from Excel Is Fun and I, Bill Jelen from MrExcel, go head to head to solve your Excel problems. In this episode, we tackle the question from Matt about using VLOOKUP to return all results and join them together with a comma space between each. This is a common issue that many Excel users face, and we have two different solutions to share with you.
My solution involves using VBA, specifically a function called GetAll. This function takes in an ID number and a range to search, and returns all matching results joined together with a comma space. It's a simple and efficient solution, but it does require some knowledge of VBA. Make sure to save your workbook as an xlsm or xlsb file in order to run VBA code.
Mike's solution involves using Advanced Filter and the TEXTJOIN function, which is only available in Excel 2016 or Office 365. This function is a game-changer for joining multiple items together, and it can even handle arrays. Mike's formula uses an IF function to filter out the items we want, and then uses TEXTJOIN to join them together with a comma space. Just remember to use Ctrl+Shift+Enter to create the formula, or use the Esc key to revert back to the original formula.
One advantage of Mike's solution is that Advanced Filter remembers the previous settings, so you can easily update the list of unique records without having to specify the input and extract ranges again. This is a great time-saving feature, and it's just one of the many reasons why Mike's book, "Ctrl+Shift+Enter: Mastering Excel Array Formulas" is a must-have for any serious Excel user. You can find it on Amazon or at your favorite bookseller.
We hope you found these solutions helpful and that you learned something new about using VLOOKUP and joining multiple results together. Don't forget to check out our other Dueling Excel episodes for more Excel tips and tricks. Thanks for watching, and we'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
#excel
#microsoft
#microsoftexcel
#exceltutorial
#excelduel
#excelchallenge
#walkthrough
#excelformula
#excelvlookup
#evergreen
#excelvba
Table of Contents:
(00:00) Join all VLOOKUP matches
(00:46) How to return all VLOOKUP matches and concatenate
(01:04) VBA solution to get all matching records
(03:43) Alternative solution using Advanced Filter and TEXTJOIN function
(09:14) Comparison of solutions
(10:54) Clicking Like really helps the algorithm
This video answers these common search terms:
how to call excel vlookup using vba
how to use concatenate with vlookup in excel
how to return all vlookup matches
how to join all vlookup matching records
return all answers from vlookup
Bill's method: Use a VBA Function called GetAll
Unique list using Remove Duplicates
Mike's method:
Unique list using Advanced Filter
TEXTJOIN function added in Office 365
TEXTJOIN(", ",,IF(OilChangeData[ID]=D2,OilChangeData[Comment],""))
Because of the IF function, the formula requires Ctrl+Shift+Enter any time you edit the formula
Alt A Q O R Enter will re-run the Advanced Filter!
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153919/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920